Hi,
I have an excel like with sheet name='sheet1'
name feb march april may
VA 1 2 3
BA 3 6 9
WA 7 5 2
Ma 4 8 8
Now i have a dataset called shru like below
Name amount
Va 2
Ma 6
where i need to put these values in the excel sheet for Va and Ma and for others i need to put zero
So,any one help me with this as to how to achieve this?
I need it ASAP
Thanks
Hi Shru,
below is my updated code. the code shows you how to convert an excel file to a sas file ,then update the sas file, then create an excel file from the updated sas file. both the excel files are saved at c:\temp.
/* code */
libname ylib "c:\temp\test.xls";
data sheet1 (drop=may);
set ylib."sheet1$"n;
name=upcase(name);
proc sort;
by name;
data shru;
input Name $ amount;
name=upcase(name);
cards;
Va 2
Ma 6
;
proc sort;
by name;
data want(rename=amount=may);
merge sheet1(in=a) shru (in=b);
by name;
if a;
if not b then amount=0;
proc print;run;
/* to export to excel file */
libname test 'c:\temp\your.xls';
proc sql;
drop table test.sheet1;
quit;
data test.sheet1;
set want;
run;
libname test clear;
libname ylib clear;
/* output */
obs name feb march april may
1 BA 3 6 9 0
2 MA 4 8 8 6
3 VA 1 2 3 2
4 WA 7 5 2 0
/* log file */
170 libname ylib "c:\temp\test.xls";
NOTE: Libref YLIB was successfully assigned as follows:
Engine: EXCEL
Physical Name: c:\temp\test.xls
171 data sheet1 (drop=may);
172 set ylib."sheet1$"n;
173 name=upcase(name);
NOTE: There were 4 observations read from the data set YLIB.'sheet1$'n.
NOTE: The data set WORK.SHEET1 has 4 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.03 seconds
174 proc sort;
175 by name;
NOTE: There were 4 observations read from the data set WORK.SHEET1.
NOTE: The data set WORK.SHEET1 has 4 observations and 4 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
176 data shru;
177 input Name $ amount;
178 name=upcase(name);
179 cards;
NOTE: The data set WORK.SHRU has 2 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
182 ;
183 proc sort;
184 by name;
185
NOTE: There were 2 observations read from the data set WORK.SHRU.
NOTE: The data set WORK.SHRU has 2 observations and 2 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
186 data want(rename=amount=may);
187 merge sheet1(in=a) shru (in=b);
188 by name;
189 if a;
190 if not b then amount=0;
WARNING: Multiple lengths were specified for the BY variable name by input data
sets. This may cause unexpected results.
NOTE: There were 4 observations read from the data set WORK.SHEET1.
NOTE: There were 2 observations read from the data set WORK.SHRU.
NOTE: The data set WORK.WANT has 4 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.03 seconds
191 proc print;run;
NOTE: There were 4 observations read from the data set WORK.WANT.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
192
193 /* to export to excel file */
194 libname test 'c:\temp\your.xls';
NOTE: Libref TEST was successfully assigned as follows:
Engine: EXCEL
Physical Name: c:\temp\your.xls
195 proc sql;
196 drop table test.sheet1;
NOTE: Table TEST.sheet1 has been dropped.
197 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
198
199 data test.sheet1;
200 set want;
201 run;
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: There were 4 observations read from the data set WORK.WANT.
NOTE: The data set TEST.sheet1 has 4 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.03 seconds
202 libname test clear;
NOTE: Libref TEST has been deassigned.
203 libname ylib clear;
NOTE: Libref YLIB has been deassigned.
you have feb-may 4 variables, so where do you want 'amount' to go? all of them?
is this helpful?
data sheet1;
input name $ feb march april ;
name=upcase(name);
cards;
VA 1 2 3
BA 3 6 9
WA 7 5 2
Ma 4 8 8
;
proc sort;
by name;
data shru;
input Name $ amount;
name=upcase(name);
cards;
Va 2
Ma 6
;
proc sort;
by name;
data want;
merge sheet1(in=a) shru (in=b);
by name;
if a;
if not b then amount=0;
proc print;run;
/* to export to excel file */
libname test 'c:\temp\test.xls';
proc sql;
drop table test.sheet1;
quit;
data test.sheet1;
set want;
run;
libname test clear;
Your code is helpfull,but as i mentioned i need to output the values in the excel.
I have a proc tabulate with values,So in excel i will search for the month column i require and update the values which is present and for others i will put zero.
It would be helpfull,if you provide me the code from where i can update the values from sas to EXCEL as how you have provided the same in the datset itself
Help me ASAP
Hi Shru,
You have to either use the exel DDE to output into specific folder or you have to export the whole dataset into same excel building the excel again.
You never answered the question of where you want the values to go. Do they represent the values for the month of May and, if so, does May really already exist as a variable in the worksheet or are you planning to add an extra month column each month?
If the above describes what you want to do, then something very close to Linlin's suggested code would work, as it is simply doing the task and then replacing Sheet1 with an updated Sheet1.
Its like every month i need to add one column in the speard sheet like this month i will fill values for last month with the existiin datset.
In spread sheet there are 20 rows but my datset from the sql will fetch the values for some of them only depending on the values.
My program will tabulate a data which i need to copy paste to that excel
So,i wanted to do automation for that,so that the column will fetch data from the existin datset,if the name are matching then it will put the values there and for other rows it will put zero.
help me how can i do this?
Thanks
Sorry. I don't know how to do that.
Hi,linlin,
Your code works fine,but i neeed the same to do fraom sas to excel using the datset condition.
It would be helpfull if you help me in this
thanks
Hi Shru,
below is my updated code. the code shows you how to convert an excel file to a sas file ,then update the sas file, then create an excel file from the updated sas file. both the excel files are saved at c:\temp.
/* code */
libname ylib "c:\temp\test.xls";
data sheet1 (drop=may);
set ylib."sheet1$"n;
name=upcase(name);
proc sort;
by name;
data shru;
input Name $ amount;
name=upcase(name);
cards;
Va 2
Ma 6
;
proc sort;
by name;
data want(rename=amount=may);
merge sheet1(in=a) shru (in=b);
by name;
if a;
if not b then amount=0;
proc print;run;
/* to export to excel file */
libname test 'c:\temp\your.xls';
proc sql;
drop table test.sheet1;
quit;
data test.sheet1;
set want;
run;
libname test clear;
libname ylib clear;
/* output */
obs name feb march april may
1 BA 3 6 9 0
2 MA 4 8 8 6
3 VA 1 2 3 2
4 WA 7 5 2 0
/* log file */
170 libname ylib "c:\temp\test.xls";
NOTE: Libref YLIB was successfully assigned as follows:
Engine: EXCEL
Physical Name: c:\temp\test.xls
171 data sheet1 (drop=may);
172 set ylib."sheet1$"n;
173 name=upcase(name);
NOTE: There were 4 observations read from the data set YLIB.'sheet1$'n.
NOTE: The data set WORK.SHEET1 has 4 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.03 seconds
174 proc sort;
175 by name;
NOTE: There were 4 observations read from the data set WORK.SHEET1.
NOTE: The data set WORK.SHEET1 has 4 observations and 4 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
176 data shru;
177 input Name $ amount;
178 name=upcase(name);
179 cards;
NOTE: The data set WORK.SHRU has 2 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
182 ;
183 proc sort;
184 by name;
185
NOTE: There were 2 observations read from the data set WORK.SHRU.
NOTE: The data set WORK.SHRU has 2 observations and 2 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
186 data want(rename=amount=may);
187 merge sheet1(in=a) shru (in=b);
188 by name;
189 if a;
190 if not b then amount=0;
WARNING: Multiple lengths were specified for the BY variable name by input data
sets. This may cause unexpected results.
NOTE: There were 4 observations read from the data set WORK.SHEET1.
NOTE: There were 2 observations read from the data set WORK.SHRU.
NOTE: The data set WORK.WANT has 4 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.03 seconds
191 proc print;run;
NOTE: There were 4 observations read from the data set WORK.WANT.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
192
193 /* to export to excel file */
194 libname test 'c:\temp\your.xls';
NOTE: Libref TEST was successfully assigned as follows:
Engine: EXCEL
Physical Name: c:\temp\your.xls
195 proc sql;
196 drop table test.sheet1;
NOTE: Table TEST.sheet1 has been dropped.
197 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
198
199 data test.sheet1;
200 set want;
201 run;
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: There were 4 observations read from the data set WORK.WANT.
NOTE: The data set TEST.sheet1 has 4 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.03 seconds
202 libname test clear;
NOTE: Libref TEST has been deassigned.
203 libname ylib clear;
NOTE: Libref YLIB has been deassigned.
Hi linlin,
I again need an help from you.
I have an excel sheet..name book1_test1 as follows.
Name jan feb march april may
A
B
C
D
E
F
which is alredy fixed.
my sas code has an proc sql which fetches data and then the dataset will be tabulated using proc tabulated. as follows:
Name value volume
A 10 1000
B 5 900
C 8 1067
Now i have used out option in the tabulate to put the tabulated results in a dataset.
Now,for some months i may get value for A,B, E and for other month i may get values for B,D only.
Now i want to check the names from the sas datset and excel so that if the name A is present in sas and also in excel then that value to be put in the excel else it sholud put zero,
I really need it.So help me in this.
Hi Shru,
If you have an excel file (book1_test1) looks like the file below and saved at c:\temp
name | jan | feb | march | april | may |
A | 1 | 2 | 3 | 4 | 5 |
B | 2 | 3 | 4 | 5 | 6 |
C | 3 | 4 | 5 | 6 | 7 |
D | 3 | 4 | 6 | 7 | 8 |
E | 1 | 3 | 4 | 5 | 9 |
F | 2 | 5 | 6 | 7 | 10 |
/******************************/;
libname ylib "c:\temp\book1_test1.xls";
data sheet1 (drop=may);
set ylib."sheet1$"n;
name=upcase(name);
proc sort data=sheet1;
by name;
data shru;
input Name $ value volume;
cards;
A 10 1000
B 5 900
C 8 1067
;
data shru; /* to change all the names to capital letters */
set shru;
name=upcase(name);
run;
proc sort data=shru;
by name;
run;
data want;
merge sheet1(in=a) shru (in=b);
by name;
if a;
if not b then do;
value=0;
volume=0;
end;
proc print;run;
/* to export to excel file */
libname test 'c:\temp\your.xls';
proc sql;
drop table test.sheet1;
quit;
data test.sheet1;
set want;
run;
libname test clear;
libname ylib clear;
Obs name jan feb march april value volume
1 A 1 2 3 4 0 0
2 B 2 3 4 5 5 900
3 C 3 4 5 6 8 1067
4 D 3 4 6 7 0 0
5 E 1 3 4 5 0 0
6 F 2 5 6 7 0 0
The code will create an excel file named your.xls at c:\temp.
you can ignore the warnings in the log file.
Hope this will solve your problem.
Thanks for your reply linlin,
But without importing the sheet from the location can i compare the names in the excel and sas dataset and put values into excel?
Thanks
Hi Shru,
Please check the link http://communities.sas.com/message/120774#120774. Haikuo provided a very good answer and explanation in his post. you can get what you want by modify his code.
Thanks- Linlin
Hi Linlin,
The code works fine but sorrry if my question was not clear.
But i have a problem as follows
my sas dataset is like
Name value volume
AA 1000 10
BB 2000 20
CC 3000 30
I have A EXCEL sheet with two tabs value and volume.and the excel sheet with value tab and volume tab is like below
Jan Feb Mar Apr may jun jul aug
AA
BB
CC
DD
EE
FF
GG
II
So,from the existing sas dataset i need to update the excel's value and volume tab with respective figures,
The prob is the excel is not havinh any name to NAME column of sas dataset.
Actually i need search for currents months column and put the values from sas dataset
And for others row which is having no figures i need to put zero.
My excel column for names is fixed but the sas datset may vary every month.So i need to match the names for both and put values,but i dont have any column name in EXCEL for name column.
It would be really helpfull if you help in this
Thanks
Shruthi
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: