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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.