topic Re: Data Step in SAS Programming

Data Step

mauri0623 — Fri, 28 Jun 2019 17:09:13 GMT

This will be my last question to ask for today.

I have the following dataset,

    ALC           ODN
7007001    GA0131a
7007002    KA0131a
7007120    ZZ0131a
7003120    DA0131a
5689020    CA0131a
4738590    PB0131a
8302345    GA0131b
8302345   ZZ0131a
8302345   DA0131a
8302345   ZZ0131a
5790234   KK1Y90
                 KFGG80                  This is where a single ALC has multiple ODNs
5892034   ZZZ1809
               ZZB1809
                 MMN09
                CVGqQ2

GXPPO0

I need to see when I have the same ALC but multiple ODNs to place those in a dataset called Exceptions.

Thank you and have a great weekend.

-mauri

Re: Data Step

ballardw — Fri, 28 Jun 2019 17:14:50 GMT

Do you need anything other than the ALC in the exceptions or do you need the ODN along with them?

Re: Data Step

Reeza — Fri, 28 Jun 2019 17:19:12 GMT

Is ALC missing sometimes as shown in your example data? Or is that a sample that assumes that values continue?

Re: Data Step

tsap — Fri, 28 Jun 2019 17:22:23 GMT

This should accomplish the results you are looking for:

DATA WORK.HAVE;
FORMAT 	 ALC $7. 	ODN $7.;
INFORMAT  	 ALC $7. 	ODN $7.;
INPUT     	 ALC 		ODN;
INFILE DATALINES DLM='|' DSD;
DATALINES;
7007001|GA0131a
7007002|KA0131a
7007120|ZZ0131a
7003120|DA0131a
5689020|CA0131a
4738590|PB0131a
8302345|GA0131b
8302345|ZZ0131a
8302345|DA0131a
8302345|ZZ0131a
5790234|KK1Y90
|KFGG80
5892034|ZZZ1809
|ZZB1809
|MMN09
|CVGqQ2
|GXPPO0
;


DATA WORK.Have (DROP= ALC2);
	SET WORK.Have;
	RETAIN ALC2;
		 IF ALC NE ' ' THEN ALC2=ALC;
	ELSE IF ALC = ' ' 	THEN ALC=ALC2;
RUN;

PROC SQL;
CREATE TABLE WORK.Exceptions	AS
	SELECT ALC, COUNT(ODN)	AS Total_ODN

	FROM WORK.HAVE
	GROUP BY ALC
	HAVING COUNT(ODN) >1
	ORDER BY ALC;
QUIT;