topic Re: Concatenate Dates to create variable names in SAS Programming

Concatenate Dates to create variable names

Zatere — Wed, 12 Aug 2020 13:31:33 GMT

Hello,

I have the following data set:

data have;
input Product_Name $ Quantity Dates :date9.;
format Dates date9.;
datalines;
A 50 01-Jun-20
A 165 01-May-20
A 10 01-Jul-20
B 200 01-Jun-20
B 200 01-May-20
B 50 01-Jul-20
C 113 01-Jun-20
C 145 01-May-20
C 23 01-Jul-20
;
run;

I want to create for each Product_Name a column that represents the total quantity in each month.

This report always holds data from the last three months.

I want to automate and make it dynamic by using the dates. Thus I have created the following table with the most recent date of the report:

data myDate;
Current_Date = '01JUL2020'D;
format Current_Date date9.;
run;

And I have created macro variables as follows:

DATA _NULL_;
set myDate;
CALL SYMPUT ('current_month',PUT(Current_Date,BEST.)); 
CALL SYMPUT ('PREVIOUS_MONTH1',intnx('month',Current_Date,-1));
CALL SYMPUT ('PREVIOUS_MONTH2',intnx('month',Current_Date,-2));

CALL SYMPUT ('current_monthV',PUT(Current_Date,yymmdd10.)); 
CALL SYMPUT ('PREVIOUS_MONTH1V',PUT(intnx('month',Current_Date,-1),yymmdd10.));
CALL SYMPUT ('PREVIOUS_MONTH2V',PUT(intnx('month',Current_Date,-2),yymmdd10.));

RUN;

I use the below code

proc sql; create table want as
SELECT Product_Name
,SUM(CASE WHEN Dates = &current_month THEN Quantity end) as "&current_monthV"n
,SUM(CASE WHEN Dates = &PREVIOUS_MONTH1 THEN Quantity end) as "&PREVIOUS_MONTH1V"n
,SUM(CASE WHEN Dates = &PREVIOUS_MONTH2 THEN Quantity end) as "&PREVIOUS_MONTH2V"n
	,SUM(Quantity) AS Quanity_TOTAL
FROM have 
group by 1
;quit;

This gives me the below:

Product_Name	2020-07-01	2020-06-01	2020-05-01	Quanity_TOTAL
A	10	50	165	225
B	50	200	200	450
C	23	113	145	281

What I want is:

Product_Name	Quantity of 2020-07-01	Quantity of 2020-06-01	Quantity of 2020-05-01	Quanity_TOTAL
A	10	50	165	225
B	50	200	200	450
C	23	113	145	281

I have tried this but it didn't work:

SUM(CASE WHEN Dates = &current_month THEN Quantity end) as cat("Quantity of ","&current_monthV")

Any ideas pleas?

Thanks

Re: Concatenate Dates to create variable names

gamotte — Wed, 12 Aug 2020 13:35:20 GMT

Hello,

... AS "Quantity of &current_monthV"n

Using invalid variable names is not a good idea though. Why don't you use labels ?

... AS my_valid_name label="Quantity of &current_monthV"

Re: Concatenate Dates to create variable names

Zatere — Wed, 12 Aug 2020 13:48:52 GMT

Thanks.
The ... AS "Quantity of &current_monthV"n worked as expected.
In terms of ... AS my_valid_name label="Quantity of &current_monthV", the label is created but the column name is not shown as wanted. So when the report is delivered is not as expected...

Re: Concatenate Dates to create variable names

gamotte — Wed, 12 Aug 2020 13:53:31 GMT

What proc do you use to produce the report ? There may be an option to use labels instead of names.

Re: Concatenate Dates to create variable names

Zatere — Wed, 12 Aug 2020 13:56:29 GMT

At the moment I just exported in excel. In the future I will be using PROC REPORT. I will make a research to find if I can use labels instead of names..

Re: Concatenate Dates to create variable names

gamotte — Wed, 12 Aug 2020 14:04:02 GMT

You can use the label option in proc export for instance :

proc export data= mySasDataset
    outfile= "c:/temp/myExport.csv" 
    dbms=csv label replace;
    putnames=yes;
run;

Re: Concatenate Dates to create variable names

gamotte — Wed, 12 Aug 2020 14:12:04 GMT

Note, proc report use variable labels as default column headers :

data have;
label x="A very interesting variable";
do x=1 to 10; output; end;
run;

proc report data=have;
column x;
define x / display;
run;