DATA Step, Macro, Functions and more

Vlookup in SAS

Reply
Contributor
Posts: 20

Vlookup in SAS

Hi All,

 

I am having troble to lookup values in SAS, I have a SAS dataset attached,

I  Extracted NAICS2D,NAICS3D,NAICS4D,NAICS5D,NAICS6D values by using substr function.I am having difficulty to look up the Title for the same digit.I am doing vlookup in Excel but due to large data set I am trying to do in SAS.

 

Please advise.

 

 

 

 

 

Super User
Posts: 17,907

Re: Vlookup in SAS

You don't quit give us enough information to help you out. To do lookups in SAS you can use either a format or a join. Depending on what your output is going to be, they each have their strong points.
Contributor
Posts: 20

Re: Vlookup in SAS

Hi Reeza,

 

Thanks for your response, Please find the attached sample output that I need to get.

I tried doing this by using format and Joins but not getting desired output.

 

Super User
Posts: 17,907

Re: Vlookup in SAS

What do you have? What are you trying to get to? Post what you've tried and isn't working.
Contributor
Posts: 20

Re: Vlookup in SAS

Reeza,

 

I Have Naics2d values 11,21,22,23,31,32,etc.

 

I need to get the Title for that, For eg :

 

If Naics2d =11 then 'Agriculture, Forestry, Fishing and Hunting'

If Naics2d=21 then 'Mining, Quarrying, and Oil and Gas Extraction' etc.

 

I am trying to code as below:

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_NAICS_OVERVIEW_0000 AS
SELECT t1.'NAICS US CODE'n,
t1.'INDUSTRY TITLE'n,
t1.NAICS2d,
t1.NAICS3d,
t1.NAICS4d,
t1.NAICS5d,
t1.NAICS6d,
t2.'INDUSTRY TITLE'n AS 'Naics2d.txt'n
FROM WORK.QUERY_FOR_NAICS_OVERVIEW t1
INNER JOIN WORK.QUERY_FOR_NAICS_OVERVIEW t2 ON (t1.'NAICS US CODE'n = t2.'NAICS US CODE'n);
QUIT;

 

Same as I need to get Title for Naics3d, Naics4d, Naics5d, Naics6d.

 

Super User
Posts: 17,907

Re: Vlookup in SAS

I echo @ballardw solution - use a format.
This is a multistage process.
First create a table that has all the mappings you need. These would be the reference tables. Then you can create formats from these tables using PROC FORMAT with CNTLIN.

Here's a good reference paper on Proc Format.

http://www2.sas.com/proceedings/sugi30/001-30.pdf
Super User
Posts: 10,538

Re: Vlookup in SAS

[ Edited ]

1) That was an Excel file, not a SAS dataset.

 

Assuming your SAS data set is named have, the variables have some relationship to the column headings (note . isn't allowed in SAS data set variable names), and that the NAICS variables are text and replacing the spaces in the column headings with _ as spaces aren'te allowed in variable names either (and I'm way too lazy to deal with the name literal syntax):

It appears that each value of NAICS is associated with text, so a format is one way to use that information

data makeformat;

   set have (rename=(NAICS_US=Start Industry_Title=Label);

   FMTNAME="NCAICS";

   type="C";

   keep start label fmtname type;

run;

proc format cntlin=makeformat;

run; 

 

Use with a put statement in a data step to assign the value.

data want;

    set have;

    length NAICS2d_txt NAICS3d_txt $ 50;

    NAICS2d_txt = put(Naics2d,$NAICS.);

    NAICS3d_txt = put(Naics3d,$NAICS.);

/* left to the interested reader add the lines to get the rest*/

run;

 

OR just use the format associated with the variables for any report procedure.

 

Bonus points for putting the format if needed later in a permanent library and finding the OPTION code to add the library to the format search path.

 

  

 

 

Ask a Question
Discussion stats
  • 6 replies
  • 868 views
  • 0 likes
  • 3 in conversation