topic Re: need help with this Tricky macro in SAS Programming

need help with this Tricky macro

rahul88888 — Wed, 18 Mar 2015 14:31:19 GMT

%let pos_summary = pos_summary_A1.csv;

%let GMP = pos_summary_A1.csv;

%let check = check_syn_new_Final.csv;

%let filter = filter.csv;

%let compare = 1; * Compare: 1=All, 2=POSvsGMP, 3=POSvsCheckSyn, 4=GMPvsCheckSyn;

%let year_1 = 011213 010414; * MMDDYY format START END eg 013113 123114;

%let year_2 = 011114 010315; * MMDDYY format START END eg 013113 123114;

%let OUTFILE = output.csv;

/******************************************************************************

******************************** INPUT ENDS ***********************************

*******************************************************************************/

%macro vars;

OPTIONS mprint mlogic merror serror minoperator;

filename pos "&pos_summary.";

filename GMP "&GMP.";

filename check "&check.";

filename flt "&filter.";

%let year_1_start=%SYSFUNC(INPUTN(%scan(&year_1.,1," "),mmddyy6.));

%let year_1_end=%SYSFUNC(INPUTN(%scan(&year_1.,2," "),mmddyy6.));

%let year_2_start=%SYSFUNC(INPUTN(%scan(&year_2.,1," "),mmddyy6.));

%let year_2_end=%SYSFUNC(INPUTN(%scan(&year_2.,2," "),mmddyy6.));

%put &year_1_start.;

%put &year_1_end.;

%put &year_2_start.;

%put &year_2_end.;

/**************************** Get headers ************************************/

%if &compare. in (1 2 3) %then %do;

DATA pos_var;

INFILE pos dsd dlm="," obs=1 lrecl=5000000;

input varname: $50. @@;

DATA _null_;

SET pos_var end=eod;

varname= upcase(varname);

call symput ('posvar' || left(_n_), varname);

if eod then call symput ('totPos', left(_n_));

%end;

%if &compare. in (1 2 4) %then %do;

DATA gmp_var;

INFILE gmp dsd dlm="," obs=1 lrecl=5000000;

input varname: $50. @@;

DATA _null_;

SET gmp_var end=eod;

varname= upcase(varname);

call symput ('gmpvar' || left(_n_), varname);

if eod then call symput ('totGmp', left(_n_));

RUN;

%end;

/**************************** Import Data ************************************/

%if &compare. in (1 2 3) %then %do;

DATA pos_summary;

INFILE pos lrecl=1000000 dsd dlm="," firstobs=2;

%DO i = 1 %TO &totPos.;

INPUT var&i.: %if &i. < 4 %then $1000.; @;

%END;

*proc print data = pos_summary (obs=1);

%end;

%if &compare. in (1 2 4) %then %do;

DATA gmp;

INFILE gmp lrecl=1000000 dsd dlm="," firstobs=2;

%DO i = 1 %TO &totGmp.;

INPUT var&i.: %if &i. < 4 %then $1000.; @;

%END;

*proc print data = gmp (obs=1);

%end;

%if &compare. in (1 3 4) %then %do;

DATA check;

INFILE check lrecl=1000000 dsd dlm="," firstobs=2;

INPUT outlet: $1000. item_no: item: $1000. fact_desc: $1000. total: year_1: year_2:;

RUN;

DATA filter;

INFILE flt lrecl=1000000 dsd dlm="," firstobs=2;

INPUT Market: $1000. name: $1000.;

RUN;

%end;

/**************************** Sort ************************************/

%if &compare. in (1 2 3) %then %do;

PROC SORT DATA = pos_summary(where=(upcase(compress(var3))="VOLUME")); BY var1-var3;

%end;

%if &compare. in (1 2 4) %then %do;

PROC SORT DATA = gmp(where=(upcase(compress(var3))="VOLUME")); BY var1-var3;

%end;

%if &compare. in (1 3 4) %then %do;

PROC SORT DATA = check(where=(upcase(compress(fact_desc))="SALESUNITS")); BY outlet item item_no;

%end;

/**************************** Transpose ************************************/

%if &compare. in (1 2 3) %then %do;

PROC TRANSPOSE DATA = pos_summary OUT = pos_summary_trans;

VAR var4-var&totPos.;

BY var1-var3;

%end;

%if &compare. in (1 2 4) %then %do;

PROC TRANSPOSE DATA = gmp OUT = gmp_trans;

VAR var4-var&totGmp.;

BY var1-var3;

%end;

%if &compare. in (1 3 4) %then %do;

PROC TRANSPOSE DATA = check OUT = check_trans (rename=(_name_=year COL1=check_volume));;

VAR year_1 year_2;

BY outlet item item_no;

RUN;

%end;

/**************************** Period Classification *************************************/

%if &compare. in (1 2 3) %then %do;

DATA pos_summary_trans;

SET pos_summary_trans;

format year $100.;

%do i = 1 %to &totPos.;

%if &i. le 3 %then %do;

rename var&i. = &&posvar&i..;

%end;

%if &i. ge 4 %then %do;

if lowcase(_name_) = "var&i." then date = mdy(substr("&&posvar&i..",1,2)*1,substr("&&posvar&i..",4,2)*1,substr("&&posvar&i..",7,2)*1);

%end;

pos_sales = col1 *1;

drop col1;

if date ge &year_1_start. and date le &year_1_end. then year="year_1";

if date ge &year_2_start. and date le &year_2_end. then year="year_2";

if year ne "";

run;

proc print data = pos_summary_trans (obs=5);format date ddmmyy6.; run;

%end;

%if &compare. in (1 2 4) %then %do;

DATA gmp_trans;

SET gmp_trans;

format year $100.;

%do i = 1 %to &totgmp.;

%if &i. le 3 %then %do;

rename var&i. = &&gmpvar&i..;

%end;

%if &i. ge 4 %then %do;

if lowcase(_name_) = "var&i." then date = mdy(substr("&&gmpvar&i..",1,2)*1,substr("&&gmpvar&i..",4,2)*1,substr("&&gmpvar&i..",7,2)*1);

%end;

gmp_sales = col1 *1;

drop col1;

if date ge &year_1_start. and date le &year_1_end. then year="year_1";

if date ge &year_2_start. and date le &year_2_end. then year="year_2";

if year ne "";

run;

proc print data = gmp_trans(obs=5); run;

%end;

/**************************** Aggregation of Facts *************************************/

proc sql;

%if &compare. in (1 2 3) %then %do;

create table pos_summary_trans_agg as

select Outlet,Item ,year, sum(pos_sales) as pos_sales

from pos_summary_trans

group by Outlet,Item,year;

%end;

%if &compare. in (1 2 4) %then %do;

create table gmp_trans_agg as

select Outlet,Item ,year, sum(gmp_sales) as gmp_sales

from gmp_trans

group by Outlet,Item,year;

%end;

%if &compare. in (1 3 4) %then %do;

create table check_trans2 as

select b.name as outlet, a.item, a.item_no, a.year, sum(a.check_volume) as check_volume

from check_trans a, filter b

where a.outlet = b.market

group by Outlet,Item,year;

%end;

quit;

/*********************** Get unique agg combination ****************/

%if &compare. in (1 2 3) %then %do;

PROC SORT NODUPKEY DATA = pos_summary_trans_agg (keep = outlet item year) out= p; by outlet item year; run;

%end;

%if &compare. in (1 2 4) %then %do;

PROC SORT NODUPKEY DATA = gmp_trans_agg (keep = outlet item year) out= g; by outlet item year; run;

%end;

%if &compare. in (1 3 4) %then %do;

PROC SORT NODUPKEY DATA = check_trans2 (keep = outlet item year) out= c; by outlet item year; run;

%end;

DATA output;

SET

%if &compare. in (1 2 3) %then %do;

p%end;

%if &compare. in (1 2 4) %then %do;

g%end;

%if &compare. in (1 3 4) %then %do;

c%end;

;

PROC SORT NODUPKEY DATA = output; by outlet item year; run;

/*********************** Final merge and calculations ****************/

%if &compare. in (1 2 3) %then %do;

PROC SORT DATA = pos_summary_trans_agg; by outlet item year; run;

%end;

%if &compare. in (1 2 4) %then %do;

PROC SORT DATA = gmp_trans_agg; by outlet item year; run;

%end;

%if &compare. in (1 3 4) %then %do;

PROC SORT DATA = check_trans2; by outlet item year; run;

%end;

DATA output;

MERGE output (in=a)

%if &compare. in (1 2 3) %then %do;

pos_summary_trans_agg (keep=outlet item year pos_sales)

%end;

%if &compare. in (1 2 4) %then %do;

gmp_trans_agg (keep=outlet item year gmp_sales)

%end;

%if &compare. in (1 3 4) %then %do;

check_trans2 (keep=outlet item year check_volume)

%end;

;

by outlet item year;

if a;

%if &compare. in (1 2 3) %then %do;

if pos_sales=. then pos_sales=0;

format pos_sales 12.2;

%end;

%if &compare. in (1 2 4) %then %do;

if gmp_sales=. then gmp_sales=0;

format gmp_sales 12.2;

%end;

%if &compare. in (1 3 4) %then %do;

if check_volume=. then check_volume=0;

format check_volume 12.2;

%end;

%if &compare. in (1 2) %then %do;

format pos_vs_gmp percent10.;

pos_vs_gmp = (pos_sales-gmp_sales)/pos_sales;

%end;

%if &compare. in (1 4) %then %do;

format gmp_vs_checksyn percent10.;

gmp_vs_checksyn = (gmp_sales-check_volume)/gmp_sales;

%end;

%if &compare. in (1 3) %then %do;

format pos_vs_checksyn percent10.;

pos_vs_checksyn = (pos_sales-check_volume)/pos_sales;

%end;

run;

PROC EXPORT DATA=output OUTFILE="&outfile." DBMS=CSV REPLACE; RUN;

%mend vars;

%vars;

Re: need help with this Tricky macro

RW9 — Wed, 18 Mar 2015 15:11:32 GMT

You do not mention what you want help with. Personally I can't look through that whole program, so I would suggest your run it, debug as far as you can (i.e. options mlogic mprint symbolgen), test parts of it, maybe even remove the macro parts and get it working as normal code. If you have a question on a specific part, then post the question with test data/required output.

Re: need help with this Tricky macro

LinusH — Wed, 18 Mar 2015 15:11:47 GMT

Are you kidding...?

Ok, what kind of help do you need? Doesn't it work?

If you didn't write it yourself, talk to the responsible.

Re: need help with this Tricky macro

Patrick — Wed, 18 Mar 2015 15:29:08 GMT

The macro as such is actually not that complicated. What is not working? What error do you get?

Re: need help with this Tricky macro

rahul88888 — Wed, 18 Mar 2015 15:35:40 GMT

There are errors at multiple levels

1st Data set is not created . Export procedure is failing.

2nd Aggregation level its saying the field is numeric while the structure says its numeric

Re: need help with this Tricky macro

Patrick — Wed, 18 Mar 2015 22:07:50 GMT

There can be many reasons for such errors and you will need to investigate and resolve the causes. You need to start with the very first error thrown and resolve it. The re-run the code and see if there are still errors and if yes resolve the next "first" error.

Use options which give you more log messages like MRPINT MERROR etc.

It's sometimes rather hard to debut SAS code generated by a macro. One way to go: Copy the MPRINT: log lines and then strip out the "MPRINT: " part. The run the remaining SAS code in a SAS Enterprise Guide session. This will allow you to better investigate if the error is caused by source data which is different than expected or SAS code generated which is logically wrong or if there are no errors on this level then you know that things go wrong on macro level.

If you don' t post your SAS log with the Errors in it then we can't give you any further advice. If you post SAS Log then please turn on options like "mprint mlogic merror source2"

Re: need help with this Tricky macro

rahul88888 — Thu, 19 Mar 2015 03:20:13 GMT

Thank You very Much for taking out time and looking at the macro

I got an error with the input files that i had provided following is the error that i got it says it has no variable called “SALESUNITS”. Instead, I have one called “SALES UNITS-192 OZ EQU CASE BASIS”.

So when sorting the table : The following “ERROR” appears.

MPRINT(VARS): PROC SORT DATA = check(where=(upcase(compress(fact_desc))="SALESUNITS"));

MPRINT(VARS): BY outlet item item_no;

MLOGIC(VARS): %IF condition &compare. in (1 2 3) is TRUE

NOTE: Input data set is empty.

NOTE: There were 0 observations read from the data set WORK.CHECK.

WHERE UPCASE(COMPRESS(fact_desc))='SALESUNITS';

NOTE: The data set WORK.CHECK has 0 observations and 7 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

After, when making the proc sql since the variable doesn’t exist merging fails.

I am changing the code so that it can accommodate this. I am just hoping it solves the error.

Thank you once again. I never expected so quick response thank you sir

Re: need help with this Tricky macro

Patrick — Thu, 19 Mar 2015 13:41:43 GMT

The code bit you've posted just tells us that there are no rows. It doesn't tell us that a variable is missing which you expect in a downstream process. So what's the actual error you're getting. If you want our support then please post the full log.