Read the file into a dataset, then use a keep statement to keep the variables you want. ... View more

FYI -- here is a link to a document which contains the concordance between I-O industry and NAICS. Go to printed page 39 (pdf page 21). http://www.bea.gov/scb/pdf/2002/12December/1202I-OAccounts2.pdf This confirms my belief that the matching between your two files should be based on NAICS code. ... View more

Could also do the following (if the intent is to produce a matrix), assuming ln_no exists on every row of the input data -- this eliminates the case statements entirely: proc sql; create table step_count as select ls_step_cd , sum (ls_step_cd = 'M17') as M17 , sum (ls_step_cd = 'K16') as K16 , sum (ls_step_cd = 'L39') as L39 , sum (ls_step_cd = 'L96') as L96 , sum (ls_step_cd = 'M60') as M60 , sum (ls_step_cd = 'L58') as L58   , sum ( ls_step_cd = 'L44') as L44 , sum (ls_step_cd = 'L52') as L52 /* everything else bucket -- results will vary if where clause is changed */ , sum (ls_step_cd not in ('M17', 'K16', 'L39', 'L96', 'M60', 'L58', 'L44', 'L52')) as  X from lm_steps where ls_actual_compl_dt between &pmb and &pme       and ls_step_cd in ('M17','K16','L39','L96','M60','L58','L44','L52') group by ls_step_cd order by ls_step_cd; quit; ... View more

FYI -- your revised NAICS97 table is still incomplete: it does not have every possible NAICS code. However, that may be irrelevant depending on where you are pulling the data from and what you are trying to accomplish. ... View more

Looks like Q1983 is trying to create a matrix, that would look something like this (I've added some fictional ls_step_cd codes and bolding for emphasis):   ls_step_cd M17 K16 L39 L96 M60 L58 L44 L52 X K16 0 31 0 0 0 0 0 0 0 L39 0 0 3 0 0 0 0 0 0 L44 0 0 0 0 0 0 2 0 0 L52 0 0 0 0 0 0 0 99 0 L58 0 0 0 0 0 101 0 0 0 L96 0 0 0 5 0 0 0 0 0 M17 25 0 0 0 0 0 0 0 0 M60 0 0 0 0 17 0 0 0 0 R01 0 0 0 0 0 0 0 0 7 R02 0 0 0 0 0 0 0 0 13 R03 0 0 0 0 0 0 0 0 5 R04 0 0 0 0 0 0 0 0 45 R99 0 0 0 0 0 0 0 0 18   Reeza's code needs a few small tweaks: case when ls_step_cd = 'M17' then count(ln_no) else 0 end as  M17 case when ls_step_cd = 'K16' then count(ln_no) else 0 end as  K16. . . . so on and so forth . . . /* case statement to deal with all other codes -- assigned to X bucket */ CASE when ln_step_cd not in ( 'M17', 'K16', 'L39', 'L96', 'M60', 'L58', 'L44', 'L52' ) then count ( ln_no ) else 0 end as  X ... View more

As an aside, I doubt that the I-O codes are more detailed than NAICS: the I-O codes are basically a collapsed version of NAICS. To reiterate my previous point, I don't think you can use the I-O code to match to NAICS codes, except crudely. For example, I-O industry 1111A0 Oilseed farming is related to NAICS codes 11111 and 11112 according to your I-O table. Based on this sample, you could assume that the first four digits of the I-O code match the first 4 digits of the NAICS code in the NAICS97 table. But, I-O industry 1111B0 Grain farming also begins with 1111; however, it is related to different NAICS codes (11113, 11114, 11115, 11116, and 11119). Matching between the first four digits of the I-O code and the first four digits in the NAICS97 table in the above two examples would return erroneous matches. Given that you are trying to match the I-O file and the NAICS97 file for whatever reason, then I suggest that you match the "related 1997 NAICS codes" from the I-O file to the codes in the NAICS97 file. A couple of different ways you could do this: split the I-O data so that you have one row for every "related 1997 NAICS code". For example, you could split the row for 1111A0 Oilseed farming into two rows: one for NAICS 11111 and one for 11112. Then you can match NAICS to NAICS. alternatively, read the "related 1997 NAICS codes" in the I-O table into a dataset with separate columns for each related NAICS code. Then, match NAICS97 based on an "IN" comparison to that range of columns. I strongly suggest you do not use the I-O codes to attempt exact matches to the NAICS table unless you are ok with fuzzy matches. ... View more

The matching keys to NAICS already seem to exist in the I-O table. The exception seems to be at the I-O sector (no I-O code) and subsector level (4 digit I-O codes). But . . . Setting the exceptions aside for now . . . .In your I-O table, all I-O codes below the sector and subsector level have one-to-many columns of matching NAICS codes. Note the NAICS codes in your I-O table are not necessarily at the lowest level of classification. (NAICS is broken down by sectors (2-digit), subsector (3-digit), industry groups (4-digit), industries (5-digit) and "national industries" (6-digit)). Each NAICS "code" in your I-O table can be matched to the corresponding NAICS codes, at the appropriate level of detail, in a table having a complete listing of NAICS codes. But, your NAICS97 table does not have a complete enumeration of all NAICS codes. For example, I-O industry 115000 (Agriculture and forestry support activities) shows that NAICS subsector 115 (Support Activities for Agriculture and Forestry) is related. (This implies that all 6-digit codes in NAICS subsector 115 are related to I-O industry 115000). But your NAICS97 table has no entries starting with 115. Is NAICS97 deliberately incomplete (missing sector, subsector, industry group, industry or national industry)??   In any case, I do not think you can use the I-O code alone to match to NAICS, except very crudely at the 4-digit level. (Results would be inaccurate in many cases). You need to use the NAICS codes on your I-O table. ... View more

Have you taken a look at formats like BESTDw.p? http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a003171706.htm ... View more

Just to confirm: You want to compare every column with every adjacent column and create a separate flag variable for each pairwise comparison? Then, you want to compare every other column with the first matching column (of each pairwise comparison) if the values of two adjacent columns match? Presumably, you will want a separate flag variable for each of these subsequent pairwise comparisons? ... View more