turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- ENCODE EXCEL FORMULA IN SAS

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-03-2015 11:25 AM

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))

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to chennupriya

03-03-2015 11:57 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to chennupriya

03-03-2015 12:18 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-03-2015 12:48 PM

HHi

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