DATA Step, Macro, Functions and more

How to retain previous column data

Reply
Contributor
Posts: 29

How to retain previous column data

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.  

PROC Star
Posts: 288

Re: How to retain previous column data

[ Edited ]

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
PROC Star
Posts: 288

Re: How to retain previous column data

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?

Ask a Question
Discussion stats
  • 2 replies
  • 130 views
  • 0 likes
  • 2 in conversation