Hi there,
I'm relatively new to SAS but just wondering if SAS has the ability to add record.
I have an Excel table contain lines of records. Because we need to make information complete, for each record in an Excel table, I need to add one additional line based on few logics. (I can do this manually, but it takes long to complete)
I know that an Excel macro can help me do the job if my logics are correctly built in, but in a general sense, does SAS have the ability to add record into existing record table? Not sure if I'm asking the right people.
Thanks
Yes, SAS can append records to an Excel dataset. There are at least two methods. Try the following (after adjusting the file path to your environment) :
libname xl excel "&sasForum.\datasets\addRecord.xlsx" scan_text=no;
data xl.one;
do x = 1 to 10;
output;
end;
run;
data two;
do x = 11 to 20;
output;
end;
run;
proc append base=xl.one data=two; run;
data three;
do x = 21 to 30;
output;
end;
run;
proc sql;
insert into xl.one select * from three;
quit;
libname xl clear;
PG
Hi,
Thanks for your response.
If you have a moment to spare, my problem is described in the attached file.
This table is the initial record table into which one additional record needs to be added.
Record_ID | Assessment_Type | admission_date | Start_Date | End_Date | Break_down_Necessary | days_Counted |
2 | 3 | 4/11/2010 | 4/14/2010 | 4/16/2010 | n | 2 |
2 | 4 | 4/11/2010 | 4/16/2010 | 6/15/2010 | y | 60 |
the following table shows the added record
Record_ID | Assessment_Type | admission_Date | Start_Date | End_Date | Break_down_Necessary | days_Counted |
2 | 3 | 4/11/2010 | 4/14/2010 | 4/16/2010 | n | 2 |
2 | added | 4/11/2010 | 4/16/2010 | 6/15/2010 | added | 60 |
2 | 4 | 4/11/2010 | 6/15/2010 | 6/15/2010 | n | 0 |
So, the logic is....whenever the break down necessary column shows a y, then we need to add a row ABOVE. The first row in the 2nd table remains the same. On the added line as you can see, the start_date becomes the end_date of the previous line, and its end_date is whenever the original end_date was. On the 3rd line, the start_date and end_date is the same in this case. All three lines will have the same admission_date assigned. We get 60 for the days_counted because it's just the subtraction of start_date from end_date.
I know it may seem redundant the way that record needs to be inserted, but it will satisfy our reporting standard.
Anyone able to assist? Can this be done in SAS platform?
Thank you
data new;
set orig;
if time_to_add = 1 then output new;
run;
data combined;
set orig new;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.