BookmarkSubscribeRSS Feed
Attyslogin
Obsidian | Level 7

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.  

2 REPLIES 2
collinelliot
Barite | Level 11

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
collinelliot
Barite | Level 11

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 809 views
  • 0 likes
  • 2 in conversation