I have an excel file, and the first 3 columns have blank cells because they have the same values as the above row. I can import it in to SAS and wonder how to add back the values for each blank cells.
I tried lag but it only added values to the first blank row but the rest are still blank. Please advise.
Areacode | subject | date | order # | Item | Price |
1 | A001 | 6/19/2024 | A0001 | XXXX1 | xx |
A0002 | XXXX2 | xx | |||
A0003 | XXXX3 | xx | |||
A0004 | XXXX4 | xx | |||
2 | A002 | 5/20/2024 | B0001 | XXXX1 | xx |
B0002 | XXXX2 | xx | |||
B0003 | XXXX3 | xx | |||
B0004 | XXXX4 | xx | |||
3 | A003 | 6/1/2024 | C0001 | XXXX1 | xx |
C0002 | XXXX2 | xx | |||
C0003 | XXXX3 | xx | |||
4 | A004 | 4/20/2024 | D0001 | XXXX1 | xx |
D0002 | XXXX2 | xx |
If this is a one time import I would say edit the Excel file.
In a SAS data step, use the RETAIN statment, to keep values from previous observations, and assign them to the current observation.
Untested code (since you didn't attach readable data):
data want;
set have;
retain prev_areacode $8 prev_subject $8 prev_date 8;
if areacode ne '' then prev_areacode = areacode;
else areacode = prev_areacode;
if subject ne '' then prev_subject = subject;
else subject = prev_subject;
if date ne . then prev_date = date;
else date = prev_date;
run;
If this is a one time import I would say edit the Excel file.
In a SAS data step, use the RETAIN statment, to keep values from previous observations, and assign them to the current observation.
Untested code (since you didn't attach readable data):
data want;
set have;
retain prev_areacode $8 prev_subject $8 prev_date 8;
if areacode ne '' then prev_areacode = areacode;
else areacode = prev_areacode;
if subject ne '' then prev_subject = subject;
else subject = prev_subject;
if date ne . then prev_date = date;
else date = prev_date;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.