hi i have an excel sheet with two variables
name expenditure
a 15782
b 516858
c 1686
d 152818
the values for the expenditure will keep on changing every month.
at present i am changing the values in my data step manually to check the conditions.
i am looking for a macro such that it will update the values in the data step automatically from the external excel sheet.
thanks in advance
If the excel sheet is in a standard format you can use a proc import step.
proc import datafile="path\spreadsheet.xlsx" out=want dbms=csv replace;
getnames=yes;
run;
Depends what you are doing, if the data in your dataset matches what you want in the dataset then just import the xls and overwrite your old dataset, per Murray_Court's response.
If you have a dataset and you only want to change some of the data then its slightly more complicated. First import the data as above. Then run an sql update statement, e.g.
proc sql;
update table have
set expenditure=imported_expenditure
from import_file
where name=imported_name;
quit;
Hi ,
You could possibly try UPDATE statement to reflect the changing expenditure in your dataset .
for eg , consider master dataset as
data repl;
input NAME $ EXPENDITURE;
datalines;
a 15782
b 516858
c 1686
d 152818
run;
the transaction dataset as
data rep2;
input NAME $ EXPENDITURE;
datalines;
a 1000
b 516858
c 2000
d 152818
run;
then using the update command
data rep1;
update repl rep2;
by name;
run;
The above syntax would require you to have 2 sets of data Main and transaction datasets .
Hopefully, this is solves your query .
Thanks.
Oops! I deleted this after I figured it out a few mins later, so I'm not sure this will even show up.
I thought I had tried to remove trailing blanks with %TRIM and it didn't work, but I noticed that I had a typo when I did that. Using %Trim on &userLib and then trying the new variable in the libname statement worked.
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.