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
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.
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.
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?
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...
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).
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.