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

[Nagendra]

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

[Murray_Court]

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;

[RW9]

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;

[Pinky9]

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.

[cau83]

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.