BookmarkSubscribeRSS Feed
Coa_SAs
Fluorite | Level 6

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.

 

 

 

 

 

6 REPLIES 6
Reeza
Super User
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
Fluorite | Level 6

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
Super User
What do you have? What are you trying to get to? Post what you've tried and isn't working.
Coa_SAs
Fluorite | Level 6

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
Super User
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
Super User

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.

 

  

 

 

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
  • 6 replies
  • 2001 views
  • 0 likes
  • 3 in conversation