DATA ABC;
INFILE CARDS DSD MISSOVER DLM="~";
INPUT DATE DATE9. AMT ONE TWO;
CARDS;
01MAR2017~78
;
RUN;
/* NEXT OBSERVATION = 02MAR2017~90 */
LIBNAME HOME "C:/WORKSHOP";
PROC SQL;
CREATE TABLE HOME.ABC LIKE
ABC;
QUIT;
PROC SQL;
CREATE TABLE HOME.BBC AS
SELECT DATE ,AMT,
CASE WHEN SUBSTR(PUT(DATE,DATE9.),1,2)="01" THEN AMT END AS ONE,
CASE WHEN SUBSTR(PUT(DATE,DATE9.),1,2)="02" THEN AMT END AS TWO
FROM ABC;
QUIT;
Well my requirement is
1] if today i'm running report and day of date is if "01" then its amount should populate in column named as "One".
2] Now when i would run report by tommorrow and lets say day of date is "02" then not only respective amount should populate in next column named as "Two" but also it should maintain the previous column amount data.
so
Running report for 01mar2017 :
ouput should be :
date | amt | one | two
01mar2017 | 78 | 78 |
Running report for 02Mar2017 :
ouput should be :
date | amt | one | two
02mar2017 | 90 | 78 | 90
but my above code flushes previous values as i dont have data for previous date once it is passed.
i dont wanto maintain data as its huge. So no transpose and no append.
Just need to know some trick so that i will be able to retain those previous date column values.
Run the following and see if it can be adapted to meet your needs. One difference from your example is that if there is not data for the day prior, this delivers 0, not missing. One could probably address that, but I figured I'd see if this comes close first.
data have;
do date = '15DEC2016'd to '10JAN2017'd;
amt = 150 + round(rannor(8) * 30);
output;
end;
run;
%macro test;
proc sql noprint;
SELECT date INTO :dt1 - :dt99 FROM have;
%let cnt = &sqlObs;
quit;
%do i = 1 %to &cnt;
%let dayBefore = %eval(&&dt&i - 1);
proc sql;
SELECT DISTINCT max(date) AS date format date9.,
max(amt * (date = &&dt&i)) AS one,
max(amt * (date = &dayBefore)) AS two
FROM have
WHERE date in (&&dt&i, &dayBefore);
quit;
%end;
%mend test;
%test
Revisiting your specs, I clearly don't follow your "one" and "two" columns. Do you want them to be dynamically named based on the day of the month?
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: