topic Re: Offset Function SAS in SAS Programming

Offset Function SAS

PetePatel — Wed, 23 Aug 2017 08:40:30 GMT

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

Re: Offset Function SAS

RW9 — Wed, 23 Aug 2017 08:47:51 GMT

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.

Re: Offset Function SAS

PetePatel — Wed, 23 Aug 2017 08:57:10 GMT

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.

Re: Offset Function SAS

RW9 — Wed, 23 Aug 2017 09:14:01 GMT

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?

Re: Offset Function SAS

PetePatel — Wed, 23 Aug 2017 09:20:47 GMT

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

Re: Offset Function SAS

Kurt_Bremser — Wed, 23 Aug 2017 11:55:25 GMT

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-data-AKA-generate/ta-p/258712 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).