BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
zqkal
Obsidian | Level 7

Hi SASUSERs,

 

I needed to lag the column variable based on the number of month different between start and end date. Below is the input data set and desired output.

 

I appreciate your input.

 

 

input dataset: 

Type st_dt id ed_dt amnt
AA 12/1/2016 0 1/1/2017 100.00
AA 1/1/2017 1 1/1/2017 200.00
AA 2/1/2017 2 1/1/2017 300.00
AA 3/1/2017 3 1/1/2017 10.00
BB 12/1/2016 0 2/1/2017 50.00
BB 1/1/2017 1 2/1/2017 105.00
BB 2/1/2017 2 2/1/2017 210.00
BB 3/1/2017 3 2/1/2017 140.00
CC 12/1/2016 0 3/1/2017 130.00
CC 1/1/2017 1 3/1/2017 124.00
CC 2/1/2017 2 3/1/2017 242.00
CC 3/1/2017 3 3/1/2017 298.00

 

 

desired output; 

Type st_dt id ed_dt amnt
AA 12/1/2016 0 1/1/2017  
AA 1/1/2017 1 1/1/2017 100.00
AA 2/1/2017 2 1/1/2017 200.00
AA 3/1/2017 3 1/1/2017 300.00
BB 12/1/2016 0 2/1/2017  
BB 1/1/2017 1 2/1/2017  
BB 2/1/2017 2 2/1/2017 50.00
BB 3/1/2017 3 2/1/2017 105.00
CC 12/1/2016 0 3/1/2017  
CC 1/1/2017 1 3/1/2017  
CC 2/1/2017 2 3/1/2017  
CC 3/1/2017 3 3/1/2017 130.00

Here is what I started with but it is not working as I expected it.

 

%macro xyz();

 

data t2;

set inputdsn;

by type act_dte;

%do lag_value= 1 %to 70;

if not first.type and first.st_dt then do;

     if st_dt ne lag&lag_value(st_dt) then

     lag_amnt = lag&lag_value(amnt);

end;

%end;

run;

%mend;

 

%xyz();

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

Show an example of the code that was working before you attempted the macro.

If you didn't have that, then get some working for a few fixed versions of LAG.

 

Note that LAG in IF statements is very problematic for success in general.

 

And you need to expand an your "rule" as what is going is not obvious at all. Like show row by row why the value is selected in the output.

View solution in original post

3 REPLIES 3
Shmuel
Garnet | Level 18

In your code you use different variable names then given with the data: 

    deal_name  act_dte  issuance_date  are not mentioned with the data.

 

It is not clear, what are you trying to do and how dates are involved. 

What are the rules. 

 

 

 

 

 

ballardw
Super User

Show an example of the code that was working before you attempted the macro.

If you didn't have that, then get some working for a few fixed versions of LAG.

 

Note that LAG in IF statements is very problematic for success in general.

 

And you need to expand an your "rule" as what is going is not obvious at all. Like show row by row why the value is selected in the output.

Ksharp
Super User

Very interesting.

 

data have;
infile cards expandtabs truncover;
input Type $	st_dt : $20.	id	ed_dt : $20.	amnt;
cards;
AA	12/1/2016	0	1/1/2017	100.00
AA	1/1/2017	1	1/1/2017	200.00
AA	2/1/2017	2	1/1/2017	300.00
AA	3/1/2017	3	1/1/2017	10.00
BB	12/1/2016	0	2/1/2017	50.00
BB	1/1/2017	1	2/1/2017	105.00
BB	2/1/2017	2	2/1/2017	210.00
BB	3/1/2017	3	2/1/2017	140.00
CC	12/1/2016	0	3/1/2017	130.00
CC	1/1/2017	1	3/1/2017	124.00
CC	2/1/2017	2	3/1/2017	242.00
CC	3/1/2017	3	3/1/2017	298.00
;
run;


data temp;
 set have(keep=type amnt);
 by type;
 n+first.type;
 if first.type then do;
  do i=1 to n;
   _amnt=.;output;
  end;
  _amnt=amnt;output;
 end;
 else do;_amnt=amnt;output;end;
 keep type _amnt;
run;
data want;
 ina=0;
 merge have(in=ina) temp;
 by type;
 if ina;
run;

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
  • 3 replies
  • 1478 views
  • 0 likes
  • 4 in conversation