topic Re: ENCODE EXCEL FORMULA IN SAS in SAS Enterprise Guide

ENCODE EXCEL FORMULA IN SAS

chennupriya — Tue, 03 Mar 2015 16:25:21 GMT

hI i HAVE An EXCEL FORMULA WHICH I NEED TO BUILD UP IN SAS

CAN U PLS LET M EKNOW THE CODING

IF (FL2=0,0 ,VLOOKUP(V2,'XY SPLIT' !$A$4:$F$11,6,FALSE))

Re: ENCODE EXCEL FORMULA IN SAS

Reeza — Tue, 03 Mar 2015 16:57:28 GMT

I'm assuming you want to convert the Excel formula to SAS code?

Basically, there isn't a direct formula to match the Excel formula. This appears to be a lookup and there are many ways to accomplish this in SAS, two common ways are a Table Join and a Format. The lookup table doesn't look too big, 7 values, so you could also build a set of it/then statements.

I would create a format from the data in A4-F11 which is a lookup table.

Then would use a if/then to apply the format.

If you need more help you'll need to provide sample data/output.

Re: ENCODE EXCEL FORMULA IN SAS

Tom — Tue, 03 Mar 2015 17:18:49 GMT

So VLOOKUP allows you to index into a particular row in a table and pull the value from a particular column in that table.

Your example appears to want to use the value in cell V2 as the index value and it wants to take the value from the 6th column.

You can convert the values of column1 and column6 in your lookup table into the START and LABEL values for a format. Then you could use the PUT() function to generate the value that the format returns for that value.

PUT(V2,myfmt.)

You could convert the values in your VLOOKUP table into a SAS dataset with an index and use the SET statement with the KEY= option to index into the proper row.

Re: ENCODE EXCEL FORMULA IN SAS

chennupriya — Tue, 03 Mar 2015 17:48:59 GMT

HHi

i will provide sample data then I can better understand I think