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.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.