Data Step

[mauri0623]

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

[ballardw]

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

[Reeza]

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

[tsap · Accepted solution]

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;