topic Re: Need help in matching the obs from dataset to excel in ODS and Base Reporting

Need help in matching the obs from dataset to excel

shru — Tue, 20 Mar 2012 16:57:19 GMT

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

Need help in matching the obs from dataset to excel

Haikuo — Tue, 20 Mar 2012 17:10:31 GMT

you have feb-may 4 variables, so where do you want 'amount' to go? all of them?

Need help in matching the obs from dataset to excel

Linlin — Tue, 20 Mar 2012 17:28:53 GMT

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;

Need help in matching the obs from dataset to excel

shru — Wed, 21 Mar 2012 06:43:08 GMT

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

Need help in matching the obs from dataset to excel

manojinpec — Wed, 21 Mar 2012 08:38:19 GMT

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.

Need help in matching the obs from dataset to excel

Linlin — Wed, 21 Mar 2012 12:42:29 GMT

Sorry. I don't know how to do that.

Need help in matching the obs from dataset to excel

art297 — Wed, 21 Mar 2012 13:14:05 GMT

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.

Need help in matching the obs from dataset to excel

Keith — Wed, 21 Mar 2012 15:10:42 GMT

If I understand your question correctly then you want to populate the 'may' column in your spreadsheet with the values from the 'Shru' dataset, setting any missing values to 0.

If you add a Named Range to your data in Excel then you can access it via the Libname Excel engine as if it was a SAS dataset. You can then use the MODIFY statement to update the spreadsheet with the new values (you can't use MERGE as SAS is not able to overwrite exisiting data).

You need to make sure that the spreadsheet data is sorted by 'name' and that the values of 'name' are the same case in both Excel and SAS.

Hopefully the code below will help, I'm unable to test it as I don't have the required licence to access Excel in this way. I've used Option Missing = 0 to show the missing values as 0, although I'm not sure if this will work.

There's also a useful paper on the Libname Excel engine. http://www2.sas.com/proceedings/sugi31/024-31.pdf

libname test 'c:\temp\test.xls' scan_text=no;

proc sort data=shru;

by name;

run;

option missing=0;

data test.named_range;

modify test.named_range shru (rename=(amount=may));

by name;

run;

option missing=.;

Need help in matching the obs from dataset to excel

shru — Wed, 21 Mar 2012 17:53:18 GMT

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

Re: Need help in matching the obs from dataset to excel

shru — Wed, 21 Mar 2012 17:57:22 GMT

Hi Keith,

Its like i already have a datset with some values in rows but i have an excel with more rows,

So i need to match the names in the excel and dataset and then put the values to the excel if both the names are same ,and for other rows i need to put as zero.

Thanks

Need help in matching the obs from dataset to excel

shru — Wed, 21 Mar 2012 17:59:13 GMT

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

Need help in matching the obs from dataset to excel

Linlin — Wed, 21 Mar 2012 22:34:19 GMT

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.

Need help in matching the obs from dataset to excel

shru — Fri, 23 Mar 2012 15:54:24 GMT

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

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.

Re: Need help in matching the obs from dataset to excel

Linlin — Fri, 23 Mar 2012 16:38:38 GMT

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.

Re: Need help in matching the obs from dataset to excel

shru — Fri, 23 Mar 2012 17:15:34 GMT

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

Re: Need help in matching the obs from dataset to excel

Linlin — Sat, 24 Mar 2012 17:27:10 GMT

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

Re: Need help in matching the obs from dataset to excel

Tom — Sat, 24 Mar 2012 17:38:48 GMT

Since you want the result in Excel it might be easier to just do the lookup in Excel.

Dump your existing SAS datasets into an Excel table.

In your Excel sheet use the HLOOKUP() function to find the values from the new sheet with the data from SAS.

Re: Need help in matching the obs from dataset to excel

shru — Thu, 29 Mar 2012 02:56:16 GMT

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

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

Re: Need help in matching the obs from dataset to excel

Linlin — Thu, 29 Mar 2012 13:10:25 GMT

can you post your excel file?

Re: Need help in matching the obs from dataset to excel

shru — Sun, 01 Apr 2012 06:40:44 GMT

Hi linlin,

I can't post my excel as its in my project linlin,

but its the same where i have asked the question.

Thanks