-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathme-macros.sas
7482 lines (6396 loc) · 310 KB
/
me-macros.sas
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
/********************************************************************/
/* ANTIDUMPING MARKET ECONOMY */
/* MACROS PROGRAM */
/* */
/* GENERIC VERSION LAST UPDATED AUGUST 13, 2024 */
/* */
/********************************************************************/
/* GENERAL MACROS */
/*------------------------------------------------------------------*/
/* G1_RUNTIME_SETUP */
/* G2_TITLE_SETUP */
/* G3_COST_TIME_MVARS */
/* G4_LOT */
/* G5_DATE_CONVERT */
/* G6_CHECK_SALES */
/* G7_EXRATES */
/* G8_FIND_NOPRODUCTION */
/* G9_COST_PRODCHARS */
/* G10_TIME_PROD_LIST IN QUARTERLY COST SITUATIONS */
/* G11_CREATE_TIME_COST_DB IN QUARTERLY COST SITUATIONS */
/* G12_ZERO_PROD_IN_PERIODS IN QUARTERLY COST SITUATIONS */
/* G13_CREATE_COST_PROD_TIMES IN QUARTERLY COST SITUATIONS */
/* G14_HIGH_INFLATION */
/* G15_CHOOSE_COSTS */
/* G16_MATCH_NOPRODUCTION IN NON-HIGH INFLATION SITUATIONS OR */
/* NON-QUARTERLY COST SITUATIONS */
/* G17_FINALIZE_COSTDATA */
/* G18_DEL_ALL_WORK_FILES */
/* G19_PROGRAM_RUNTIME */
/*------------------------------------------------------------------*/
/* COMPARISON MARKET MACROS */
/*------------------------------------------------------------------*/
/* HM1_PRIME_MANUF_MACROS */
/* HM2_MIXEDCURR */
/* HM3_ARMSLENGTH */
/* HM4_CEPTOT */
/* HM5_COSTTEST */
/* HM6_DATA_4_WTAVG */
/* HM7_WTAVG_DATA */
/* HM8_CVSELL */
/* HM9_LOTADJ */
/*------------------------------------------------------------------*/
/* MARGIN PROGRAM MACROS */
/*------------------------------------------------------------------*/
/* US1_MACROS */
/* US2_SALETYPE */
/* US3_USD_CONVERSION */
/* US4_INDCOMM */
/* US5_CEPRATE */
/* US6_ENTVALUE */
/* US7_CONCORDANCE */
/* US8_LOTADJ */
/* US9_OFFSETS */
/* US10_LOT_ADJUST_OFFSETS */
/* US11_CVSELL_OFFSETS */
/* US12_COMBINE_P2P_CV */
/* US13_COHENS_D_TEST */
/* US14_WT_AVG_DATA */
/* US15_RESULTS */
/* US16_CALC_CASH_DEPOSIT */
/* US17_MEANINGFUL_DIFF_TEST */
/* US18_ASSESSMENT */
/********************************************************************/
/********************************************************/
/* G-1: SET UP MACRO VARIABLES FOR RUN TIME CALCULATION */
/********************************************************/
%MACRO G1_RUNTIME_SETUP;
DATA _NULL_;
CALL SYMPUT('BDAY', STRIP(PUT(DATE(), DOWNAME.)));
CALL SYMPUT('BWDATE', STRIP(PUT(DATE(), WORDDATE18.)));
CALL SYMPUT('BTIME', STRIP(PUT(TIME(), TIMEAMPM8.)));
CALL SYMPUT ('BDATE',PUT(DATE(),DATE.));
CALL SYMPUT('BDATETIME', (STRIP(PUT(DATETIME(), 20.))));
RUN;
%PUT NOTE: This program started running on &BDAY, &BWDATE at &BTIME..;
%MEND G1_RUNTIME_SETUP;
%GLOBAL INDEX_SOURCE TIME_OUTSIDE_POR TIME_ANNUALIZED;
%LET INDEX_SOURCE = NA;
%LET TIME_OUTSIDE_POR = NA;
%LET TIME_ANNUALIZED = NA;
/***********************************************************/
/* G-2: CREATE FIXED TITLES AND FOOTNOTES ON LINES 1 AND 2 */
/* FOR EACH PRINT AND PROGRAM-SPECIFIC MACROS FOR TITLES 3 */
/* AND HIGHER */
/***********************************************************/
%MACRO G2_TITLE_SETUP;
%GLOBAL PROGRAM SALES_DB CALC_TYPE;
%IF %UPCASE(&SALESDB) = HMSALES %THEN
%DO;
%LET PROGRAM = HOME MARKET;
%LET SALES_DB = HOME MARKET;
%END;
%ELSE
%IF %UPCASE(&SALESDB) = USSALES %THEN
%DO;
%LET PROGRAM = U.S. SALES MARGIN;
%LET SALES_DB = U.S.;
%END;
%IF %UPCASE(&CASE_TYPE) = AR %THEN
%DO;
%LET CALC_TYPE = RESULTS;
%END;
%ELSE
%IF %UPCASE(&CASE_TYPE) = INV %THEN
%DO;
%LET CALC_TYPE = DETERMINATION;
%END;
%ELSE
%DO;
%LET CALC_TYPE = ;
%END;
TITLE1 "&PROGRAM PROGRAM - &PRODUCT FROM &COUNTRY (&BEGINPERIOD - &ENDPERIOD)";
TITLE2 "&SEGMENT &STAGE FOR RESPONDENT &RESPONDENT (&CASE_NUMBER)";
FOOTNOTE1 "*** BUSINESS PROPRIETARY INFORMATION SUBJECT TO APO ***";
FOOTNOTE2 "&BDAY, &BWDATE - &BTIME";
%MEND G2_TITLE_SETUP;
/*****************************************/
/* G3: CREATE MACROS FOR QUARTERLY COSTS */
/*****************************************/
%MACRO G3_COST_TIME_MVARS;
%GLOBAL HM_TIME_PERIOD NAF_TIME_PERIOD COST_TIME_PERIOD US_TIME_PERIOD
US_TIME AND_TIME EQUAL_TIME OR_TIME FIRST_TIME COP_TIME_OUT
COST_PERIODS TIME_ANNUAL ANNUAL_COST;
/*--------------------------------------------*/
/* Define format for different kinds of Cost. */
/*--------------------------------------------*/
PROC FORMAT;
VALUE $TIMETYPE
'AN' = 'Reported Annualized'
'SG' = 'Surrogate Annualized'
'RQ' = 'Reported Quarterly'
'BQ' = 'Blended Surrogate Quarterly'
'IQ' = 'Indexed Surrogate Quarterly'
'H1' = 'Reported High Inflation'
'H2' = 'Annualized High Inflation'
'H3' = 'Surrogate High Inflation'
'CA' = 'Calculated'
'NA' = 'N/A';
RUN;
%IF %UPCASE(&COMPARE_BY_TIME) = YES %THEN
%DO;
%LET AND_TIME = AND;
%LET EQUAL_TIME = =;
%LET OR_TIME = OR;
%LET FIRST_TIME = FIRST.&US_TIME_PERIOD;
%LET US_TIME = US_TIME_PERIOD; /* For Level of Trade calculation */
/* in CM program. */
%LET COST_PERIODS = COST PERIODS;
%LET HM_TIME_PERIOD = HM_TIME_PERIOD;
%IF %UPCASE(&TIME_ANNUALIZED) = NA %THEN
%DO;
%LET TIME_ANNUAL = ;
%LET ANNUAL_COST = ;
%END;
%IF %UPCASE(&TIME_ANNUALIZED) NE NA %THEN
%DO;
%LET TIME_ANNUAL = ,&TIME_ANNUALIZED;
%LET ANNUAL_COST = ANNUALCOST;
%END;
%IF %UPCASE(&SALESDB) = HMSALES %THEN
%DO;
%LET NAF_TIME_PERIOD = NAF_TIME_PERIOD;
%LET COP_TIME_OUT = COST_TIME_PERIOD;
%END;
%IF %UPCASE(&SALESDB) = USSALES %THEN
%DO;
%LET HM_TIME_PERIOD = HM_TIME_PERIOD;
%END;
%END;
%IF %UPCASE(&COMPARE_BY_TIME) = NO %THEN
%DO;
%LET HM_TIME_PERIOD = ;
%LET COST_TIME_PERIOD = ;
%LET NAF_TIME_PERIOD = ;
%LET US_TIME_PERIOD = ;
%LET US_TIME = ;
%LET AND_TIME = ;
%LET EQUAL_TIME = ;
%LET COP_TIME_OUT = ;
%LET COST_PERIODS = ;
%LET TIME_ANNUAL = ;
%LET ANNUAL_COST = ;
%END;
%MEND G3_COST_TIME_MVARS;
/*******************************************************************/
/* G4: CREATE LEVEL OF TRADE VARIABLE FOR PROGRAMMING USE BASED ON */
/* INFORMATION IN MACRO VARIABLE 'LET HMLOT = <???>' */
/*******************************************************************/
%MACRO G4_LOT(LOT_REP, LOT_PROG);
%IF %UPCASE(&LOT_REP) NE NA %THEN
%DO;
&LOT_PROG = &LOT_REP;
%END;
%ELSE
%IF %UPCASE(&LOT_REP) EQ NA %THEN
%DO;
&LOT_PROG = 0;
%END;
%MEND G4_LOT;
/*******************************************************************/
/* G-5: TEST SALE DATE VARIABLE FORMAT */
/* */
/* If your date variable is not in SAS date format, the language */
/* below will attempt to convert it. If the conversion is not */
/* successful, please contact a SAS support person for assistance. */
/*******************************************************************/
%MACRO DATE_CONVERT(DATE, TYPE);
%GLOBAL SALEDATE_FORMAT DATE_FORMAT&TYPE;
DATA _NULL_;
SET &SALESDB;
IF _N_=1 THEN
DO;
LENGTH SALEDATEFORM $4;
SALEDATEFORM = VFORMATN(&DATE);
IF SUBSTR(SALEDATEFORM,1,1) = '$' THEN
SALEDATE_FORMAT_TYPE = 'CHARACTER';
ELSE
IF SALEDATEFORM IN( 'DATE','WEEK','YYMM', 'MMDD','WORD') THEN
SALEDATE_FORMAT_TYPE = 'DATE';
ELSE
SALEDATE_FORMAT_TYPE = 'NUMERIC';
CALL SYMPUT ('SALEDATE_FORMAT',SALEDATE_FORMAT_TYPE);
END;
RUN;
%IF &SALEDATE_FORMAT = DATE %THEN
%DO;
%LET DATE_FORMAT&TYPE = YES;
%END;
/*********************************************/
/* Attempt to convert the sale date variable */
/* to date format, when necessary. */
/*********************************************/
%IF &SALEDATE_FORMAT NE DATE %THEN
%DO;
DATA &SALESDB;
SET &SALESDB (RENAME = (&DATE = DATE_TEMP));
FORMAT &DATE DATE9.;
&DATE = INPUT(DATE_TEMP, ANYDTDTE21.);
RUN;
/************************************************/
/* Retest the format of converted date variable */
/* to see if conversion was successful. */
/************************************************/
DATA _NULL_;
LENGTH CONVERTDATEFORM $4;
SET &SALESDB;
IF _N_ = 1;
CONVERTDATEFORM = VFORMATN(&DATE);
IF CONVERTDATEFORM IN('DATE','WEEK','YYMM', 'MMDD','WORD') THEN
CONVERTDATE = 'YES';
ELSE
CONVERTDATE = 'NO';
CALL SYMPUT('CONVERT_SUCCESS', CONVERTDATE);
RUN;
%IF &CONVERT_SUCCESS = YES %THEN
%DO;
%LET DATE_FORMAT&TYPE = YES;
PROC PRINT DATA = &SALESDB (OBS = &PRINTOBS) SPLIT="*";
VAR DATE_TEMP &DATE;
LABEL DATE_TEMP = "ORIGINAL*DATE*VARIABLE*==========="
&DATE = "CONVERTED*DATE*VARIABLE*===========";
TITLE3 "CHECK OF SALE DATE VARIABLE CONVERSION, FIRST &PRINTOBS OBSERVATIONS";
TITLE5 "If converted data is missing or not correct, further action will be required.";
TITLE6 "If original data is missing and the converted date is 01JAN1960, then variable &date is uninitialized.";
RUN;
%END;
%ELSE
%IF &CONVERT_SUCCESS = NO %THEN
%DO;
%LET DATE_FORMAT&TYPE = NO;
PROC PRINT DATA = &SALEDB (OBS = &PRINTOBS) SPLIT="*";
VAR DATE_TEMP &DATE;
LABEL DATE_TEMP = "ORIGINAL*DATE*VARIABLE*==========="
&DATE = "CONVERTED*DATE*VARIABLE*===========";
TITLE3 "THE AUTOMATIC ATTEMPT TO CONVERT &DATE, A &SALEDATE_FORMAT VARIABLE, WAS NOT SUCCESSFUL.";
TITLE5 "Note: Before exchange rates can be merged or month designations assigned in administrative,";
TITLE6 "reviews, the variable will have to be converted to SAS date format by an alternative method.";
RUN;
%END;
%END;
%MEND DATE_CONVERT;
%MACRO G5_DATE_CONVERT;
%IF %UPCASE(&SALESDB) = HMSALES %THEN
%DO;
%DATE_CONVERT(&HMSALEDATE, )
%END;
%ELSE
%IF %UPCASE(&SALESDB) = DOWNSTREAM %THEN
%DO;
%DATE_CONVERT(&HMSALEDATE, )
%END;
%ELSE
%IF %UPCASE(&SALESDB) = USSALES %THEN
%DO;
%DATE_CONVERT(&USSALEDATE, )
%IF %UPCASE(&FILTER_EP) = YES %THEN
%DO;
%DATE_CONVERT(&EP_DATE_VAR,_EP)
%END;
%IF %UPCASE(&FILTER_CEP) = YES %THEN
%DO;
%DATE_CONVERT(&CEP_DATE_VAR,_CEP)
%END;
%END;
%MEND G5_DATE_CONVERT;
/*****************************************************************************/
/* G-6: CHECK SALES FOR NEGATIVE PRICES AND QUANTITIES, DATES OUTSIDE PERIOD */
/*****************************************************************************/
%MACRO G6_CHECK_SALES;
%GLOBAL MONTH;
%LET MONTH = ; /* Null value for macro variables &HMMONTH or &USMONTH for investigations. */
%MACRO CHECK_SALES(SALES_MONTH, QTY,GUP, DATE, SALES, DBTYPE, DB, BEGINDAY, ENDDAY);
DATA &SALES NEGDATA_&DB OUTDATES_&DB;
SET &SALES;
IF &QTY LE 0 OR &GUP LE 0 THEN
OUTPUT NEGDATA_&DB;
ELSE
IF "&BEGINDAY."D GT &DATE OR &DATE GT "&ENDDAY."D THEN
OUTPUT OUTDATES_&DB;
%MARGIN_FILTER
ELSE
DO;
/*-------------------------------------------------------*/
/* In administrative reviews, define HMMONTH and USMONTH */
/* variables so that each month has a unique value. */
/*-------------------------------------------------------*/
%IF (%UPCASE(&CASE_TYPE) EQ AR) %THEN
%DO;
%IF &DBTYPE = US %THEN
%DO;
%LET BEGIN = &HMBEGINWINDOW;
%END;
%ELSE
%DO;
%LET BEGIN = &HMBEGINDAY;
%END;
MON = MONTH(&DATE);
YRDIFF = YEAR(&DATE) - YEAR("&BEGIN."D);
&SALES_MONTH = MON + YRDIFF * 12;
DROP MON YRDIFF;
%LET MONTH = &SALES_MONTH;
%END;
OUTPUT &SALES;
END;
RUN;
PROC PRINT DATA = NEGDATA_&DB (OBS = &PRINTOBS);
TITLE3 "SAMPLE OF &DBTYPE SALES WITH GROSS PRICE (&GUP) OR QUANTITY (&QTY) LESS THAN OR EQUAL TO ZERO";
TITLE5 "NOTE: Default programming removes these sales from the calculations.";
TITLE6 "Should this not be appropriate, adjust accordingly.";
RUN;
PROC PRINT DATA = OUTDATES_&DB (OBS = &PRINTOBS);
TITLE3 "SAMPLE OF &DBTYPE SALES OUTSIDE THE PERIOD OF ANALYSIS";
TITLE4 "BASED ON THE VALUE OF &DATE BEING OUTSIDE THE DATE RANGE &BEGINDAY AND &ENDDAY";
TITLE6 "NOTE: Default programming removes these sales from the calculations.";
TITLE7 "Should this not be appropriate, adjust accordingly.";
RUN;
%MEND CHECK_SALES;
%IF %UPCASE(&SALESDB) = HMSALES %THEN
%DO;
%MACRO MARGIN_FILTER;
%MEND MARGIN_FILTER;
%CHECK_SALES(HMMONTH, &HMQTY, &HMGUP, &HMSALEDATE, HMSALES, HOME MARKET, HM, &HMBEGINDAY, &HMENDDAY);
%END;
%IF %UPCASE(&SALESDB) = DOWNSTREAM %THEN
%DO;
%MACRO MARGIN_FILTER;
%MEND MARGIN_FILTER;
%CHECK_SALES(HMMONTH, &HMQTY, &HMGUP, &HMSALEDATE, DOWNSTREAM, DOWNSTREAM, DS, &HMBEGINDAY, &HMENDDAY);
%END;
%IF %UPCASE(&SALESDB) = USSALES %THEN
%DO;
%MACRO MARGIN_FILTER;
%IF %UPCASE(&FILTER_CEP) = YES %THEN
%DO;
%IF &DATE_FORMAT_CEP = YES %THEN
%DO;
ELSE IF SALE_TYPE = "CEP"
AND ("&BEGINDAY_CEP."D GT &CEP_DATE_VAR OR &CEP_DATE_VAR GT "&ENDDAY_CEP."D)
THEN OUTPUT OUTDATES_&DB;
%END;
%END;
%IF %UPCASE(&FILTER_EP) = YES %THEN
%DO;
%IF &DATE_FORMAT_EP = YES %THEN
%DO;
ELSE IF SALE_TYPE = "EP"
AND ("&BEGINDAY_EP."D GT &EP_DATE_VAR OR &EP_DATE_VAR GT "&ENDDAY_EP."D)
THEN OUTPUT OUTDATES_&DB;
%END;
%END;
%MEND MARGIN_FILTER;
%CHECK_SALES(USMONTH, &USQTY, &USGUP, &USSALEDATE, USSALES, US, US, &USBEGINDAY, &USENDDAY);
%END;
%MEND G6_CHECK_SALES;
/*************************************************/
/* G-7: MERGE EXCHANGE RATES INTO SALES DATABASE */
/*************************************************/
%MACRO G7_EXRATES;
%GLOBAL EXRATE1 XRATE1 EXRATE2 XRATE2;
%MACRO MERGE_RATES(USE_EXRATES, EXDATA, EXRATE, XRATE,DATE);
/*----------------------------------------------*/
/* Set values for exchange rate macro variables */
/* when exchange rate is not required. */
/*----------------------------------------------*/
%IF %UPCASE(&USE_EXRATES) = NO %THEN
%DO;
%LET &EXRATE = ;
%LET &XRATE = 1;
%END;
/*--------------------------------------*/
/* Merge Exchange Rates, when required. */
/*--------------------------------------*/
%IF %UPCASE(&USE_EXRATES) = YES %THEN
%DO;
%LET &EXRATE = EXRATE_&EXDATA;
%LET &XRATE = EXRATE_&EXDATA;
/*----------------------------------------------------*/
/* First establish whether date variable is in proper */
/* format before attempting to merge exchange rates. */
/*----------------------------------------------------*/
%IF &DATE_FORMAT = YES %THEN
%DO;
/*---------------------------------------------------*/
/* If date variable is in proper format, merge in */
/* format before attempting to merge exchange rates. */
/*---------------------------------------------------*/
PROC SORT DATA = COMPANY.&EXDATA (RENAME = (DATE = &DATE
%IF %UPCASE(&CASE_TYPE) = INV %THEN
%DO;
&EXDATA.I = EXRATE_&EXDATA)
DROP = &EXDATA.R)
%END;
%ELSE
%IF %UPCASE(&CASE_TYPE) = AR %THEN
%DO;
&EXDATA.R = EXRATE_&EXDATA)
DROP = &EXDATA.I)
%END;
OUT = EXRATES;
BY &DATE;
RUN;
PROC SORT DATA = &SALESDB OUT = &SALESDB;
BY &DATE;
RUN;
DATA &SALESDB NORATES;
MERGE &SALESDB (IN = A) EXRATES (IN = B);
BY &DATE;
IF A & B THEN
OUTPUT &SALESDB;
ELSE IF A & NOT B THEN
OUTPUT NORATES;
RUN;
PROC PRINT DATA = NORATES;
VAR &DATE;
TITLE3 "&SALESDB WITH NO EXCHANGE RATES FOR &EXDATA";
RUN;
%END;
%END;
%MEND MERGE_RATES;
%IF %UPCASE(&SALESDB) = HMSALES %THEN
%DO;
%MERGE_RATES(&USE_EXRATES1, &EXDATA1, EXRATE1, XRATE1, &HMSALEDATE);
%MERGE_RATES(&USE_EXRATES2, &EXDATA2, EXRATE2, XRATE2, &HMSALEDATE);
%END;
%ELSE
%IF %UPCASE(&SALESDB) = USSALES %THEN
%DO;
%MERGE_RATES(&USE_EXRATES1, &EXDATA1, EXRATE1, XRATE1, &USSALEDATE);
%MERGE_RATES(&USE_EXRATES2, &EXDATA2, EXRATE2, XRATE2, &USSALEDATE);
%END;
%ELSE
%IF %UPCASE(&SALESDB) = DOWNSTREAM %THEN
%DO;
%MERGE_RATES(&USE_EXRATES1, &EXDATA1, EXRATE1, XRATE1, &HMSALEDATE);
%MERGE_RATES(&USE_EXRATES2, &EXDATA2, EXRATE2, XRATE2, &HMSALEDATE);
%END;
%MEND G7_EXRATES;
/**********************************************************/
/* G-8 IDENTIFY PRODUCTS REQUIRING SURROGATE COSTS */
/* */
/* CONNUMUs in the CM and U.S. datasets that have sales */
/* but no production in the POI/POR must be in the COP */
/* dataset with a production quantity of 0 (zero). If */
/* respondent does not report these CONNUMs in the cost */
/* dataset, the analyst must add these CONNUMs to the COP */
/* dataset with a production quantity of 0 (zero). */
/**********************************************************/
%MACRO G8_FIND_NOPRODUCTION;
/********************************************************************************/
/* G-8-A: Annualized Cost, High Inflation, OR Quarterly Cost without production */
/********************************************************************************/
%IF %UPCASE(&COMPARE_BY_HIGH_INFLATION) EQ NO %THEN
%DO;
/*************************************************************************/
/* G-8-A-i: Define macro variables and macros used in finding annualized */
/* Cost or quarterly Cost without production. */
/*************************************************************************/
%GLOBAL EQUAL_COST_PRIME NO_PROD_COST_PRIME TIME_ANNUALIZED;
%LET TIME_ANNUALIZED = NA;
/*-----------------------------------------------------------------------------------*/
/* Create null values for macro variables when Cost prime/non-prime is not relevant. */
/*-----------------------------------------------------------------------------------*/
%IF %UPCASE(&COST_PRIME) EQ NA %THEN
%DO;
%LET EQUAL_COST_PRIME = ; /* EQUAL operator for cost prime purposes */
%LET NO_PROD_COST_PRIME = ; /* no production cost prime */
%END;
%ELSE
/*---------------------------------------------------------------*/
/* Create macro variables when Cost prime/non-prime is relevant. */
/*---------------------------------------------------------------*/
%IF %UPCASE(&COST_PRIME) NE NA %THEN
%DO;
%LET EQUAL_COST_PRIME = =;
%LET NO_PROD_COST_PRIME = NO_PROD_&COST_PRIM;
%END;
PROC SORT DATA = COST OUT = COST;
BY &COST_MANF &COST_PRIM &COST_MATCH &COST_TIME_PERIOD;
RUN;
%IF %UPCASE(&COST_PROD_CHARS) = YES %THEN
%DO;
%LET PROD_CHARS = &COST_CHAR;
%END;
%ELSE
%IF %UPCASE(&COST_PROD_CHARS) = NO %THEN
%DO;
%LET PROD_CHARS = ;
%END;
%IF %UPCASE(&COMPARE_BY_TIME) = YES %THEN
%DO;
%MACRO WHERE_STMT;
%MEND WHERE_STMT;
%IF %UPCASE(&TIME_ANNUALIZED) EQ NA %THEN
%DO;
%MACRO NOPROD_TIME_TYPE;
NOPROD_TIME_TYPE = 'RQ';
%MEND NOPROD_TIME_TYPE;
%END;
%ELSE
%IF %UPCASE(&TIME_ANNUALIZED) NE NA %THEN
%DO;
%MACRO NOPROD_TIME_TYPE;
NOPROD_TIME_TYPE = 'RQ';
IF &COST_TIME_PERIOD IN(&TIME_ANNUALIZED) THEN
NOPROD_TIME_TYPE = "AN";
%MEND NOPROD_TIME_TYPE;
%END;
%MACRO RENAME_TIME_TYPE;
&COST_TIME_PERIOD = NO_PRODUCTION_QUARTER
%MEND RENAME_TIME_TYPE;
%END;
%ELSE
%IF %UPCASE(&COMPARE_BY_TIME) = NO %THEN
%DO;
%MACRO WHERE_STMT;
%MEND WHERE_STMT;
%MACRO NOPROD_TIME_TYPE;
NOPROD_TIME_TYPE = "NA";
%MEND NOPROD_TIME_TYPE;
%MACRO RENAME_TIME_TYPE;
%MEND RENAME_TIME_TYPE;
%END;
/************************************************************/
/* G-8-A-ii: Find total production quantity for each model. */
/************************************************************/
PROC MEANS NWAY DATA = COST NOPRINT;
CLASS &COST_MANF &COST_PRIM &COST_MATCH &COST_TIME_PERIOD;
%WHERE_STMT
VAR &COST_QTY;
OUTPUT OUT = TOTPRODQTY (DROP = _:)
SUM = TOT_CONNUM_PROD_QTY;
RUN;
/****************************************************/
/* G-8-A-iii: Separate out Cost without production. */
/****************************************************/
DATA COST (DROP = TOT_CONNUM_PROD_QTY
RENAME = (NOPROD_TIME_TYPE = COST_TIME_TYPE))
NOPRODUCTION (KEEP = &COST_MANF &COST_PRIM &COST_MATCH
&PROD_CHARS NOPROD_TIME_TYPE &COST_TIME_PERIOD
RENAME = (&COST_MANF &EQUAL_COST_MANF &NO_PROD_COST_MANF
&COST_PRIM &EQUAL_COST_PRIME &NO_PROD_COST_PRIME
&COST_MATCH = NO_PRODUCTION_CONNUM
%RENAME_TIME_TYPE));
MERGE COST (IN = A) TOTPRODQTY (IN = B);
BY &COST_MANF &COST_PRIM &COST_MATCH &COST_TIME_PERIOD;
IF A;
%NOPROD_TIME_TYPE
IF TOT_CONNUM_PROD_QTY LE 0 THEN
OUTPUT NOPRODUCTION;
ELSE
OUTPUT COST;
RUN;
PROC CONTENTS DATA = NOPRODUCTION
OUT = NOPROD (KEEP = NOBS) NOPRINT;
RUN;
DATA _NULL_;
SET NOPROD;
IF _N_ = 1;
IF NOBS GT 0 THEN
MATCH_NOPRODS = "YES";
ELSE
IF NOBS = 0 THEN
MATCH_NOPRODS = "NO";
CALL SYMPUT('FIND_SURROGATES', MATCH_NOPRODS);
RUN;
%END;
%ELSE
/*************************************************/
/* G-8-B: High inflation Cost Without Production */
/*************************************************/
%IF %UPCASE(&COMPARE_BY_HIGH_INFLATION) EQ YES %THEN
%DO;
/****************************************************/
/* G-8-B-i: Define macro variables used in finding */
/* high inflation Cost without production. */
/****************************************************/
%GLOBAL COST_MFR_HP COST_MFR_HP_RENAME /* 8 macro variables */
COST_MFR_HP_RENAME_BACK COST_MFR_HP_SAME COST_PRIME_HP
COST_PRIME_HP_RENAME COST_PRIME_HP_RENAME_BACK COST_PRIME_HP_SAME;
/*------------------------------------------------------------*/
/* Create null macro variable values when CM sales */
/* manufacturer or Cost manufacturer is not relevant. */
/*------------------------------------------------------------*/
%LET COST_MFR_HP = ; /* Cost manufacturer variable */
%LET COST_MFR_HP_RENAME = ; /* Rename cost mfr variable to no-prod cost mfr variable */
%LET COST_MFR_HP_RENAME_BACK = ; /* Rename no-prod cost mfr variable to cost mfr variable */
%LET COST_MFR_HP_SAME = ; /* If no-prod cost mfr variable equal cost mfr variable */
/*---------------------------------------------------------*/
/* Assign macro variable values when CM sales manufacturer */
/* and Cost manufacturer are relevant. */
/*---------------------------------------------------------*/
%IF %UPCASE(&HMMANUF) NE NA AND %UPCASE(&COST_MANUF) NE NA %THEN /* When there is reported CM and Cost mfr */
%DO;
%LET COST_MFR_HP = &COST_MANUF;
%LET COST_MFR_HP_RENAME = &COST_MANUF = NO_PROD_MFR;
%LET COST_MFR_HP_RENAME_BACK = NO_PROD_MFR = &COST_MANUF;
%LET COST_MFR_HP_SAME = &COST_MANUF = NO_PROD_MFR;
%END;
/*-----------------------------------------------------------------*/
/* Assign null macro variable values when CM sales Prime/Non-prime */
/* or Cost Prime/Non-prime are not relevant. */
/*-----------------------------------------------------------------*/
%LET COST_PRIME_HP = ; /* Cost prime variable */
%LET COST_PRIME_HP_RENAME = ; /* Rename Cost prime variable to no-prod cost prime variable */
%LET COST_PRIME_HP_RENAME_BACK = ; /* Rename no-prod prime mfr variable to cost prime variable */
%LET COST_PRIME_HP_SAME = ; /* If no-prod prime variable to cost prime variable */
/*-------------------------------------------------------*/
/* Assign macro variables values when CM Prime/Non-prime */
/* and Cost Prime/Non-prime are relevant. */
/*-------------------------------------------------------*/
%IF %UPCASE(&HMPRIME) NE NA AND %UPCASE(&COST_PRIME) NE NA %THEN /* When there is reported CM and Cost prime */
%DO;
%LET COST_PRIME_HP = &COST_PRIME;
%LET COST_PRIME_HP_RENAME = &COST_PRIME = NO_PROD_PRIME;
%LET COST_PRIME_HP_RENAME_BACK = NO_PROD_PRIME = &COST_PRIME_HP;
%LET COST_PRIME_HP_SAME = &COST_PRIME = NO_PROD_PRIME;
%END;
/************************************************************/
/* G-8-B-ii: Find total production quantity for each model. */
/************************************************************/
PROC MEANS NWAY DATA = COST NOPRINT;
CLASS &COST_MFR_HP &COST_PRIME_HP &COST_MATCH &COST_YEAR_MONTH;
VAR &COST_QTY;
OUTPUT OUT = TOTPRODQTY (DROP = _:) SUM = TOT_CONNUM_PROD_QTY;
RUN;
/****************************************************/
/* G-8-B-iii: Separate out Cost without production. */
/****************************************************/
PROC SORT DATA = COST OUT = COST;
BY &COST_MFR_HP &COST_PRIME_HP &COST_MATCH &COST_YEAR_MONTH;
RUN;
DATA COST (DROP = TOT_CONNUM_PROD_QTY)
NOPRODUCTION (KEEP = &COST_MFR_HP &COST_PRIME_HP &COST_MATCH
&COST_YEAR_MONTH &COST_CHAR
RENAME = (&COST_MFR_HP_RENAME &COST_PRIME_HP_RENAME
&COST_MATCH = NO_PROD_CONNUM
&COST_YEAR_MONTH = NO_PROD_YEAR_MONTH));
MERGE COST TOTPRODQTY;
BY &COST_MFR_HP &COST_PRIME_HP &COST_MATCH &COST_YEAR_MONTH;
IF TOT_CONNUM_PROD_QTY LE 0 THEN
OUTPUT NOPRODUCTION;
ELSE
OUTPUT COST;
RUN;
PROC CONTENTS DATA = NOPRODUCTION
OUT = NOPROD (KEEP = NOBS) NOPRINT;
RUN;
DATA _NULL_;
SET NOPROD;
IF _N_ = 1;
IF NOBS GT 0 THEN
MATCH_NOPRODS = "YES";
ELSE
IF NOBS = 0 THEN
MATCH_NOPRODS = "NO";
CALL SYMPUT('FIND_SURROGATES', MATCH_NOPRODS);
RUN;
%END;
%MEND G8_FIND_NOPRODUCTION;
/*****************************************************************/
/* G-9 ATTACH PRODUCT CHARACTERISTIC TO COST DATA, WHEN REQUIRED */
/*****************************************************************/
%MACRO G9_COST_PRODCHARS;
%GLOBAL PROD_MATCH;
%LET PROD_MATCH = ;
%IF %UPCASE(&FIND_SURROGATES) = YES AND %UPCASE(&COST_PROD_CHARS) = NO %THEN
%DO;
%MACRO GETCHARS;
%IF %UPCASE(&SALESDB) = HMSALES %THEN
%DO;
%LET PROD_MATCH = &HMCPPROD;
PROC SORT DATA = USSALES NODUPKEY OUT = USCONNUMLIST (KEEP = &USCVPROD &USCHAR);
BY &USCVPROD;
RUN;
DATA USCONNUMLIST;
SET USCONNUMLIST;
RENAME &USCVPROD = &HMCPPROD;
%MACRO RENAMECHARS;
%LET I = 1;
%LET RENAMECALC = ;
%DO %UNTIL (%SCAN(&USCHAR, &I, %STR( )) = %STR());
%LET RENAMECALC = &RENAMECALC
RENAME %SYSFUNC(COMPRESS(%SCAN(&USCHAR,&I, %STR( ))))
= %SYSFUNC(COMPRESS(%SCAN(&HMCHAR,&I, %STR( )))) %NRSTR(;);
&RENAMECALC
%LET I = %EVAL(&I + 1);
%END;
%MEND RENAMECHARS;
%RENAMECHARS;
RUN;
PROC SORT DATA = HMSALES NODUPKEY OUT = HMCONNUMLIST (KEEP = &HMCPPROD &HMCHAR);
BY &HMCPPROD;
RUN;
DATA CONNUMLIST;
SET HMCONNUMLIST USCONNUMLIST;
RUN;
PROC SORT DATA = CONNUMLIST OUT = CONNUMLIST NODUPKEY;
BY &HMCPPROD;
RUN;
%END;
%IF %UPCASE(&SALESDB) = USSALES %THEN
%DO;
%LET PROD_MATCH = &USCVPROD;
PROC SORT DATA = USSALES NODUPKEY OUT = CONNUMLIST (KEEP = &USCVPROD &USCHAR);
BY &USCVPROD;
RUN;
%END;
%MEND GETCHARS;
%GETCHARS;
%MACRO ATTACH_CHARS(SALES_MATCH);
PROC SORT DATA = COST OUT = COST;
BY &COST_MATCH &COST_TIME_PERIOD;
RUN;
DATA COST COST_NOT_SALES;
MERGE COST (IN=A ) CONNUMLIST (IN=B RENAME=(&SALES_MATCH = &COST_MATCH));
BY &COST_MATCH;
IF A & B THEN OUTPUT COST;
IF A & NOT B THEN OUTPUT COST_NOT_SALES;
RUN;
DATA NOPRODUCTION;
MERGE NOPRODUCTION (IN = A )
CONNUMLIST (IN = B
RENAME = (&SALES_MATCH = NO_PRODUCTION_CONNUM));
BY NO_PRODUCTION_CONNUM;
IF A & B;
RUN;
%MEND ATTACH_CHARS;
%IF %UPCASE(&SALESDB) = HMSALES %THEN
%DO;
%ATTACH_CHARS(&HMCPPROD);
%END;
%IF %UPCASE(&SALESDB) = USSALES %THEN
%DO;
%ATTACH_CHARS(&USCVPROD);
%END;
%END;
%MEND G9_COST_PRODCHARS;
/**********************************************************************/
/* G-10. FILL IN MISSING LINES IN QUARTERLY COST DATA, WHEN REQUIRED */
/**********************************************************************/
/**********************************************************************************************/
/* For cases where there are timespecific costs, i.e. quarterly cost, the program assigns */
/* quarters to the CM and U.S. sales observations. The quarters are based off of the reported */
/* sale date, and first day of the POR/POI. The quarters are character, length 2, with the */
/* values '-2', '-1', '0', '1', '2', etc. This will only run if there is quarterly cost. */
/**********************************************************************************************/
%MACRO CREATE_QUARTERS(SLDT, PROGRAM);
%GLOBAL HM_TIME_PERIOD US_TIME_PERIOD;
%LET HM_TIME_PERIOD = ;
%LET US_TIME_PERIOD = ;
%IF %UPCASE(&COMPARE_BY_TIME) EQ YES %THEN
%DO;
%LET US_TIME_PERIOD = QTR;
%IF &SALESDB = HMSALES %THEN
%DO;
%LET HM_TIME_PERIOD = QTR;
%END;
%ELSE
%IF &SALESDB = USSALES %THEN
%DO;
%LET HM_TIME_PERIOD = HM_TIME_PERIOD;
%END;
FIRSTMONTH =(MONTH("&BEGINPERIOD"D));
MTH = (MONTH(&SLDT) + (YEAR(&SLDT) - YEAR("&BEGINPERIOD"D)) * 12);
NQTR = (1 + (FLOOR((MTH - FIRSTMONTH) / 3))) ;
QTR = STRIP(PUT(NQTR, 2.));
DROP FIRSTMONTH MTH NQTR;
%END;
%MEND CREATE_QUARTERS;
/**********************************************************************************************************************/
/* */
/* For cases where there are time specific costs, i.e. quarterly cost, the program takes the following steps: */
/* 1. In section G-10; */
/* a. Makes a list of CONNUMS and time periods with reported sales */
/* b. Makes a list of time periods with reported costs. */
/* c. Checks to see if there are periods with sales that have no reported costs. */
/* d. Makes a POR weight average of the cost database by CONNUM. */
/* 2. In Section G-11, if there are periods that have sales that do not have any reported costs: */
/* a. On a CONNUM specific basis, pull the closest period of production into the period that needs production. */
/* Keep the direct materials costs, and index them to adjust those costs according to the period. */
/* b. Apply the POR/I weight average conversion costs i.e. the non-direct materials costs, to each CONNUM. */
/* 3. In Section G-12; if there CONNUMS with no production in a specific period, but production in other period(s): */
/* a. Find the most similar CONNUM with production in the period and assign its direct materials costs as */
/* surrogate to the CONNUM(s) with no production. */
/* b. Assign the POR/I weight average conversion costs to those CONNUMS with no production in the period. */
/* 4. In Section G-13; if there are CONNUMS with sales in the POR/I and no production anywhere in the POR/I: */
/* a. Assign the most similar CONNUMs cost from within the period as the surrogate for the CONNUM with no costs. */
/* */
/**********************************************************************************************************************/
%MACRO G10_TIME_PROD_LIST;
OPTIONS MPRINT SYMBOLGEN;
%IF %UPCASE(&COMPARE_BY_TIME) EQ YES %THEN
%DO;
%GLOBAL NOPROD_CHAR DIF_CHAR RENAME_NOPROD RENAME_DIF RENAME_HMCHAR RENAME_USCHAR ALLCOSTVARS NOPROD_TO_CHAR
MFRZ LIST_TIMES ZERO_PROD_TIME NEEDTIMELST NV_TYPE SUM_DIRMAT_VARS REPLACE_INDEXED_DIRMATS NOPRDDMT
CLSTPRDDMT PCTCHADMT RDMT HM_TIMES COSTPROD;
%MACRO SCENERIO_MCRS;
%LET RENAME_USCHAR = ;
/* Scenario 1: CM, COP DB has COST_CHARS */
%MACRO HM_COP_HAS_CHARS;
DATA CHAR_CHANGE (DROP = I);
DO I = 1 TO COUNTW("&COST_CHAR");
NOPROD_CHAR = STRIP(CATS(SCAN("&COST_CHAR", I), "_NOPROD"));
DIF_CHAR = STRIP(CATS(SCAN("&COST_CHAR", I), "_DIF"));
RENAME_NOPROD = STRIP(CATS(SCAN("&COST_CHAR", I), "=", NOPROD_CHAR));
RENAME_DIF = STRIP(CATS(SCAN("&COST_CHAR", I), "=", DIF_CHAR));
RENAMEMATCHUS= STRIP(CATS(SCAN("&USCHAR", I), "=", SCAN("&COST_CHAR", I)));
NOPROD_TO_CHAR= STRIP(CATS(NOPROD_CHAR, "=", SCAN("&COST_CHAR", I)));
RENAMEMATCHHM = STRIP(CATS(SCAN("&HMCHAR", I), "=", SCAN("&COST_CHAR", I)));
OUTPUT;
END;
RUN;
PROC SQL NOPRINT;
SELECT NOPROD_CHAR, DIF_CHAR, RENAME_NOPROD, RENAME_DIF, RENAMEMATCHHM, RENAMEMATCHUS, NOPROD_TO_CHAR
INTO :NOPROD_CHAR SEPARATED BY " ",
:DIF_CHAR SEPARATED BY " ",
:RENAME_NOPROD SEPARATED BY " ",
:RENAME_DIF SEPARATED BY " ",
:RENAME_HMCHAR SEPARATED BY " ",
:RENAME_USCHAR SEPARATED BY " ",
:NOPROD_TO_CHAR SEPARATED BY " "
FROM CHAR_CHANGE;