Developing proc informat set up

[wlierman]

This question is building off of a solution from ballardw(Super User) regarding building a proc informat approach to labeling employment titles.

I have many, many lines of type_firm descriptions a sampling will look something like in Excel

Agricultural_worker                         = 2003

Apparel_Manufacturing                   = 2004

Beverage_Manufacturing                = 2002

Cabinets_Woodworking                   = 2005

Construction_Homes_Residential    = 3002

(the codes are what I am using to delineate the different firm types)

and so on I guess I have two questions

1) can I just copy and paste these rows into my data step - actually the Proc format

(and I have ~ 940 rows and Services will have well over 1500)

and 2

Once step number 1 is successful the should look like

Proc format

invalue Firm_type (upcase)

Agricultural_worker                         = 2003

Apparel_Manufacturing                   = 2004

Beverage_Manufacturing                = 2002

Cabinets_Woodworking                   = 2005

Construction_Homes_Residential    = 3002   and so on

how would I code so that " " can be added to the start and end of the type firm to look like

"Agricultural_worker"                         = 2003

"Apparel_Manufacturing"                   = 2004

"Beverage_Manufacturing"                = 2002

"Cabinets_Woodworking"                  = 2005

"Construction_Homes_Residential"    = 3002

Another thing just occurred to me the codes on the excel spread sheet look like '= then the no

so the ' before the = needs to be removed too.

This is a long convoluted question, but I appreciate your help and assistance.

wlierman

[Reeza]

Do you know that you can create a format from a data set instead of trying to generate proc format code? Or do you want to generate the code for some reason?

A quick way to figure out how to configure the data set is to make a small data set, pipe that out and then use CNTLIN to create the dataset.

*assuming you have created your firm type format;
proc format cntlout=demo;
select FIRM_TYPE;
run;

Create a data set that's similar using your imported data from Excel.

Then create the format using:

proc format cntlin=yourDataset;
run;

References:
https://support.sas.com/resources/papers/proceedings/proceedings/forum2007/068-2007.pdf
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/n1e19y6lrektafn1kj6nbvhus59w.htm

[wlierman · Accepted solution]

Thank you for the information I think between your answer and Tom's answer that these are solutions.

[Tom]

Don't understand what you are saying you currently have.

Do you have text that looks like your first example? Including the equal sign?  Why would text like that be in an EXCEL spreadsheet?  Would it be more useful to have two columns in the EXCEL sheet.  One with the name and one with the code?

Something like this dataset.

data metadata;
  input code $ name $40.;
cards;
2003 Agricultural_worker
2004 Apparel_Manufacturing
2002 Beverage_Manufacturing
2005 Cabinets_Woodworking
3003 Construction_Homes_Residential
;

Which you could easily convert into a CNTLIN dataset to create an FORMAT (if you need CODE to be numeric define and INFORMAT instead) and then use PROC FORMAT to read in that dataset and define the FORMAT.

data cntlin;
  fmtname='$CODE';
  set metadata;
  start = name;
  label = code ;
run;

proc format cntlin=cntlin;
run;

Which you could then use to encode your current names into code.

data want;
  set have;
  code = put(name,$code.);
run;

[wlierman]

Thank you for the response.  Both you and Reeza are on the same track.
I will set up the code as you both have described.
Thank you.
wlierman