Dear all
if I have a current table as
SIC_3_CODE | SIC_3_description | SIC_4_CODE | SIC_4_description |
124 | COAL MINING SERVICES | ||
1241 | COAL MINING SERVICES | ||
131 | CRUDE PETROLEUM AND NATURAL GAS | ||
1311 | CRUDE PETROLEUM AND NATURAL GAS | ||
132 | NATURAL GAS LIQUIDS | ||
1321 | NATURAL GAS LIQUIDS | ||
138 | OIL AND GAS FIELD SERVICES | ||
1381 | DRILLING OIL AND GAS WELLS | ||
1382 | OIL AND GAS FIELD EXPLORATION SERVICES | ||
1389 | OIL AND GAS FIELD SERVICES, NOT ELSEWHERE CLASSIFIED
|
and expect to get table like
SIC_3_CODE | SIC_3_description | SIC_4_CODE | SIC_4_description |
124 | COAL MINING SERVICES | 1241 | COAL MINING SERVICES |
131 | CRUDE PETROLEUM AND NATURAL GAS | 1311 | CRUDE PETROLEUM AND NATURAL GAS |
132 | NATURAL GAS LIQUIDS | 1321 | NATURAL GAS LIQUIDS |
138 | OIL AND GAS FIELD SERVICES | 1381 | DRILLING OIL AND GAS WELLS |
138 | OIL AND GAS FIELD SERVICES | 1382 | OIL AND GAS FIELD EXPLORATION SERVICES |
138 | OIL AND GAS FIELD SERVICES | 1389 | OIL AND GAS FIELD SERVICES, NOT ELSEWHERE CLASSIFIED |
in order to get this result, I expect to
if the below rows are blank then file it with the above strings.
and then delete rows if the value of SIC_4_CODE is blank.
could you please give me some suggestions about this?
thanks in advance.
This might do it:
data sic3;
set have;
where sic_3_code ne '';
run;
data sic4;
set have;
where sic_4_code ne '';
sic_3_code = substr(sic_4_code,1,3);
run;
data want;
merge
sic3
sic4
;
by sic_3_code;
run;
Untested; for tested code, provide example data in a data step with datalines, so the dataset can easily be recreated.
Is it always alternating rows or do you fully populated rows too?
This seems to come from a badly imported source. Can't you fix this upstream in the process?
Fully agree to @ChrisNZ: this looks like a defect created while importing the data. Can you provide the current table as data step using datalines? I think it is possible to get what you need by using retain + rename.
Please show how you created that dataset. The problem is very likely found there.
This might do it:
data sic3;
set have;
where sic_3_code ne '';
run;
data sic4;
set have;
where sic_4_code ne '';
sic_3_code = substr(sic_4_code,1,3);
run;
data want;
merge
sic3
sic4
;
by sic_3_code;
run;
Untested; for tested code, provide example data in a data step with datalines, so the dataset can easily be recreated.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.