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

@Kurt_Bremser

 

Hi,

Can you please help me with this output. 

Data I have:

 

IDTr_IDCategoryLowHigh
1111Age34
1112Duration33
1113Duration22
2221Age6060
2222Duration2424

 

What I get:

 

IDTr_IDCategoryLowHighAge_LowAge_High
1111Age3434
1112Duration3367
1113Duration2255
2221Age60606060
2222Duration24248484

 

What I need:

 

IDTr_IDCategoryLowHighAge_LowAge_High
1111Age3434
1112Duration3367
1113Duration2289
2221Age60606060
2222Duration24248484

 

Code I use:

data need;

set have;

if Category= 'Duration' then Age_Low=0;

         Age_Low=lag(low)+low;

if Category= 'Duration' then Age_High=0;

         Age_High=lag(high)+High;

if Category ='Age' then    Age_Low= low;

if Category ='Age' then    Age_High= high;

run;

 

How many ever rows I might have with category as 'Duration' I should be able to take the previous row value and sum this with current row. 

 

But my code stops executing this logic after first occurrence. Please advise.

 

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
hhinohar
Quartz | Level 8

Use retain statement to do rolling calculation by id.

 

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=lestmtsref&docsetTarget=p...

 

Below example uses ifn function but if statement works as well.

 

data have;
infile datalines dlm="09"x;
input ID Tr_ID Category $ Low High;
datalines;
111	1	Age	3	4
111	2	Duration	3	3
111	3	Duration	2	2
222	1	Age	60	60
222	2	Duration	24	24
;
run;

data want;
	set have;
	by id;
	*first keep values retained;
	retain age_low age_high;
	*if first.id then input low and high values else do rolling calc;
	age_low=ifn(first.id,low,age_low+low);
	age_high=ifn(first.id,high,age_high+high);
run;


View solution in original post

2 REPLIES 2
hhinohar
Quartz | Level 8

Use retain statement to do rolling calculation by id.

 

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=lestmtsref&docsetTarget=p...

 

Below example uses ifn function but if statement works as well.

 

data have;
infile datalines dlm="09"x;
input ID Tr_ID Category $ Low High;
datalines;
111	1	Age	3	4
111	2	Duration	3	3
111	3	Duration	2	2
222	1	Age	60	60
222	2	Duration	24	24
;
run;

data want;
	set have;
	by id;
	*first keep values retained;
	retain age_low age_high;
	*if first.id then input low and high values else do rolling calc;
	age_low=ifn(first.id,low,age_low+low);
	age_high=ifn(first.id,high,age_high+high);
run;


PeterClemmensen
Tourmaline | Level 20
data have;
input ID Tr_ID Category $ Low High;
datalines;
111 1 Age      3 4
111 2 Duration 3 3
111 3 Duration 2 2
222 1 Age      60 60
222 2 Duration 24 24
;

data want;
   set have;
   by id;
   if first.ID then call missing(age_low, age_high);
   age_low + Low;
   age_high + High;
run;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 507 views
  • 0 likes
  • 3 in conversation