topic if the below rows are blank then file it with the above strings. in SAS Programming

if the below rows are blank then file it with the above strings.

Alexxxxxxx — Wed, 04 Mar 2020 01:56:59 GMT

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.

Re: if the below rows are blank then file it with the above strings.

ChrisNZ — Wed, 04 Mar 2020 03:58:56 GMT

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?

Re: if the below rows are blank then file it with the above strings.

andreas_lds — Wed, 04 Mar 2020 05:53:34 GMT

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.

Re: if the below rows are blank then file it with the above strings.

Kurt_Bremser — Wed, 04 Mar 2020 08:02:48 GMT

Please show how you created that dataset. The problem is very likely found there.

Re: if the below rows are blank then file it with the above strings.

Kurt_Bremser — Wed, 04 Mar 2020 08:08:51 GMT

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.