Vlookup in SAS

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

[Reeza]

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.

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

[Reeza]

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

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

[Reeza]

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

[ballardw]

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.