Need help in matching the obs from dataset to excel

[Linlin]

can you post your excel file?

[shru]

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

[Keith]

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=.;

[shru]

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

[Tom]

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.