topic Re: Vlookup in SAS in SAS Programming

Vlookup in SAS

Coa_SAs — Fri, 18 Dec 2015 19:11:03 GMT

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.

Re: Vlookup in SAS

Reeza — Fri, 18 Dec 2015 19:50:55 GMT

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.

Re: Vlookup in SAS

Coa_SAs — Fri, 18 Dec 2015 20:06:48 GMT

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.

Re: Vlookup in SAS

Reeza — Fri, 18 Dec 2015 20:12:29 GMT

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

Re: Vlookup in SAS

Coa_SAs — Fri, 18 Dec 2015 20:36:04 GMT

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.

Re: Vlookup in SAS

ballardw — Fri, 18 Dec 2015 21:13:12 GMT

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.

Re: Vlookup in SAS

Reeza — Fri, 18 Dec 2015 20:45:34 GMT

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