topic Re: Need a Macro to update the values in the datastep automatically from an excel sheet in SAS Programming

Need a Macro to update the values in the datastep automatically from an excel sheet

Nagendra — Fri, 21 Mar 2014 07:55:20 GMT

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

Re: Need a Macro to update the values in the datastep automatically from an excel sheet

Murray_Court — Fri, 21 Mar 2014 09:19:14 GMT

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;

Re: Need a Macro to update the values in the datastep automatically from an excel sheet

RW9 — Fri, 21 Mar 2014 09:28:56 GMT

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;

Re: Need a Macro to update the values in the datastep automatically from an excel sheet

Pinky9 — Fri, 21 Mar 2014 11:22:32 GMT

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.

Re: Need a Macro to update the values in the datastep automatically from an excel sheet

cau83 — Fri, 21 Mar 2014 12:46:15 GMT

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.