BookmarkSubscribeRSS Feed
PetePatel
Quartz | Level 8

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

PetePatel
Quartz | Level 8

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

PetePatel
Quartz | Level 8

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

Kurt_Bremser
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 4347 views
  • 0 likes
  • 3 in conversation