BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Alexxxxxxx
Pyrite | Level 9

Dear all

 

if I have a current table as 

SIC_3_CODESIC_3_descriptionSIC_4_CODESIC_4_description
124COAL MINING SERVICES  
  1241 COAL MINING SERVICES
131CRUDE PETROLEUM AND NATURAL GAS  
  1311 CRUDE PETROLEUM AND NATURAL GAS
132NATURAL GAS LIQUIDS  
  1321 NATURAL GAS LIQUIDS
138OIL 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_CODESIC_3_descriptionSIC_4_CODESIC_4_description
124COAL MINING SERVICES1241 COAL MINING SERVICES
131CRUDE PETROLEUM AND NATURAL GAS1311 CRUDE PETROLEUM AND NATURAL GAS
132NATURAL GAS LIQUIDS1321 NATURAL GAS LIQUIDS
138OIL AND GAS FIELD SERVICES1381 DRILLING OIL AND GAS WELLS
138OIL AND GAS FIELD SERVICES1382 OIL AND GAS FIELD EXPLORATION SERVICES
138OIL AND GAS FIELD SERVICES1389 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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

4 REPLIES 4
ChrisNZ
Tourmaline | Level 20

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?

andreas_lds
Jade | Level 19

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.

Kurt_Bremser
Super User

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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1043 views
  • 1 like
  • 4 in conversation