topic Re: Proc import: how to add back blank values due to the same values as previous records in SAS Programming

Proc import: how to add back blank values due to the same values as previous records

fengyuwuzu — Thu, 20 Jun 2024 02:47:36 GMT

I have an excel file, and the first 3 columns have blank cells because they have the same values as the above row. I can import it in to SAS and wonder how to add back the values for each blank cells.

I tried lag but it only added values to the first blank row but the rest are still blank. Please advise.

Areacode	subject	date	order #	Item	Price
1	A001	6/19/2024	A0001	XXXX1	xx
			A0002	XXXX2	xx
			A0003	XXXX3	xx
			A0004	XXXX4	xx
2	A002	5/20/2024	B0001	XXXX1	xx
			B0002	XXXX2	xx
			B0003	XXXX3	xx
			B0004	XXXX4	xx
3	A003	6/1/2024	C0001	XXXX1	xx
			C0002	XXXX2	xx
			C0003	XXXX3	xx
4	A004	4/20/2024	D0001	XXXX1	xx
			D0002	XXXX2	xx

Re: Proc import: how to add back blank values due to the same values as previous records

LinusH — Thu, 20 Jun 2024 08:11:50 GMT

If this is a one time import I would say edit the Excel file.

In a SAS data step, use the RETAIN statment, to keep values from previous observations, and assign them to the current observation.

Untested code (since you didn't attach readable data):

data want;
	set have;
	retain prev_areacode $8 prev_subject $8 prev_date 8;
	if areacode ne '' then prev_areacode = areacode;
	else areacode = prev_areacode;
	if subject ne '' then prev_subject = subject;
	else subject = prev_subject;
	if date ne . then prev_date = date;
	else date = prev_date;
run;

Re: Proc import: how to add back blank values due to the same values as previous records

fengyuwuzu — Thu, 20 Jun 2024 16:49:33 GMT

Thank you very much. LinusH