DATA Step, Macro, Functions and more

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

Reply
Frequent Learner
Posts: 1

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

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

Contributor
Posts: 45

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

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;


Super User
Super User
Posts: 7,397

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

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;

Contributor
Posts: 35

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

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.

Contributor
Posts: 69

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

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.

Ask a Question
Discussion stats
  • 4 replies
  • 493 views
  • 0 likes
  • 5 in conversation