12-18-2015 02:11 PM
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.
12-18-2015 02:50 PM
12-18-2015 03:06 PM
12-18-2015 03:36 PM
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:
CREATE TABLE WORK.QUERY_FOR_NAICS_OVERVIEW_0000 AS
SELECT t1.'NAICS US CODE'n,
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);
Same as I need to get Title for Naics3d, Naics4d, Naics5d, Naics6d.
12-18-2015 03:45 PM
12-18-2015 03:36 PM - edited 12-18-2015 04:13 PM
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
set have (rename=(NAICS_US=Start Industry_Title=Label);
keep start label fmtname type;
proc format cntlin=makeformat;
Use with a put statement in a data step to assign the value.
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*/
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.