Need help in matching the obs from dataset to excel

[shru]

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

[Haikuo]

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

[Linlin]

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;

[shru]

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

[manojinpec]

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.

[art297]

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.

[shru]

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

[Linlin]

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

[shru]

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

[Linlin · Accepted solution]

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.

[shru]

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.

[Linlin]

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.

[shru]

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

[Linlin]

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

[shru]

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