BookmarkSubscribeRSS Feed
Nagendra
Calcite | Level 5

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

4 REPLIES 4
Murray_Court
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
Pyrite | Level 9

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1716 views
  • 0 likes
  • 5 in conversation