Offset Function SAS

Reply
Contributor
Posts: 25

Offset Function SAS

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

Super User
Super User
Posts: 9,416

Re: Offset Function SAS

Posted in reply to PetePatel

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.

 

Contributor
Posts: 25

Re: Offset Function SAS

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.

Super User
Super User
Posts: 9,416

Re: Offset Function SAS

Posted in reply to PetePatel

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?

Contributor
Posts: 25

Re: Offset Function SAS

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

Super User
Posts: 9,888

Re: Offset Function SAS

Posted in reply to PetePatel

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
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 5 replies
  • 551 views
  • 0 likes
  • 3 in conversation