BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
SASAlex101
Quartz | Level 8

I have this table; 


data WORK.input;
length ID $1. Type $10.;
infile datalines delimiter='#';
input ID $ Type $ Val sortorder;
datalines;
1#A#100#1
1#A#100#2
1#A#100#3
1#A#100#4
1#A#100#5
1#A#100#6
1#X#999999#7
1#X#999999#8
1#X#999999#9
1#X#999999#10
1#A#700#11
1#A#700#12
1#A#700#13
1#A#700#14
2#A#300#1
2#A#300#2
2#A#300#3
2#A#300#4
2#X#999999#5
2#X#999999#6
2#A#500#7
2#A#500#8
2#X#999999#9
2#A#800#10
;
run;

I've included a sort order column. What I'm trying to do is when we hit the first row where Type = X I want to retain the last known Val for each row thereafter until we hit type <> X then we will set the new Val. 

 

I have tried this code, but it appears to be working (mostly) except for lines 1, 11,21, and 24 are showing NULL or the Val on the X row. 

data work.input2(DROP=sortorder);
set work.input;
LagVal = LAG(Val);
if first.ID then LagVal = .;
retain LastKnownValBeforeX;
if Type ne 'X' then LastKnownValBeforeX = LagVal;
run;

the picture below explains my issue and desired output:

Capture.PNG

Thanks in advance; 

1 ACCEPTED SOLUTION

Accepted Solutions
SASAlex101
Quartz | Level 8

GOT IT!

data output;
set input;
by id sortorder Type;
retain newvar;
if Type='A' then newvar= val;
if Type ne 'X' then newvar = newvar;
output;
run;

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User

You need a RETAINed variable:

data want;
set input;
by id;
retain newval;
if first.id then newval = .;
if type = "X" then newval = val;
else if newval ne . then val = newval;
drop newval;
run;
SASAlex101
Quartz | Level 8

This didn't give the correct output. 99999 is repeated through the A rows... 

SASAlex101
Quartz | Level 8

GOT IT!

data output;
set input;
by id sortorder Type;
retain newvar;
if Type='A' then newvar= val;
if Type ne 'X' then newvar = newvar;
output;
run;

SASAlex101
Quartz | Level 8
Props to Kurt for helping find this solution!

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