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 = ¤t_month THEN Quantity end) as "¤t_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 = ¤t_month THEN Quantity end) as cat("Quantity of ","¤t_monthV")
Any ideas pleas?
Thanks
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.