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
- /
- SAS Programming
- /
- General Programming
- /
- Offset Function 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

08-23-2017 04:40 AM

Hello SAS Experts,

I have an Excel spreadsheet (extract attached) which needs to be transferred into SAS for automation purposes.

I have a lookup table in columns A, B and C and a dataset (columns E, F and G) and I have created an offset function in Excel to create column H (Result) which I am trying to replicate in SAS.

Any idea on how this result can be achieved in SAS?

I am more proficient in Excel than SAS so any help would be greatly appreciated.

Cheers,

Pete

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

Posted in reply to PetePatel

08-23-2017 04:47 AM

Forget Excel and Excel way of thinking. What you have here is data merging or joining if you prefer. You are taking a value from have1 and merging it to have 2 based on id=a, so somthing like (and you need to get your data into SAS in a good format-Excel is a really poor data medium):

proc sql; create table WANT as select A.*, B.C as RESULT from HAVE2 A left join HAVE1 B on A.ID=B.A; quit;

You could also do the above in datastep merge.

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

08-23-2017 04:57 AM

Thanks for your response RW9.

I understand the merging part but was more trying to find a way to calculate the offset part in SAS as it is dependent on the values in columns B, C, F and G.

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

Posted in reply to PetePatel

08-23-2017 05:14 AM

Sorry, I am not following at all then, there is no F and G in the given file? Calrify the question, post the example data in the form of a datastep in the body of the post using the code window (its the {i} above the post area). Then show what the output should be (avoiding Excel terms), and clarify the logic between the two, i.e. column named result should be the XYZ of column <> * column <> where column <> = coumn <>. Terms like "offset" are Excel specific and have no meaning outside Excel. I assume result is the product of some formula based on merging the two data?

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

08-23-2017 05:20 AM

No problem.

Columns A-C are a lookup table (assume this would need to be imported into SAS).

Columns E-G are in a SAS dataset.

Column H (Result) is what I am trying to create in SAS (You can see that it uses an offset function referencing columns F and G (named T and A) against the lookup table (Columns B and C).

So column H (Result) is what I need help to create...

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

Posted in reply to PetePatel

08-23-2017 07:55 AM

Forget the Excel way of thinking. Think in datasets and variables, not in Excel spreadsheet terms. Present your SAS datasets as they are (use the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to convert your datasets to data steps for easy posting here), and give an example of the expected resulting dataset (just like the output of a simple proc print is sufficient).

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

Maxims of Maximally Efficient SAS Programmers