Solved: Re: Traspose data with some condition

FRAFLUTE · Posted 11-19-2019 11:45 AM

Good Morning!

I have this situation:

Cust. Code	Cust Name	Contract state	Contract Number	Plate	Driver Code	Driver Name
123456	CUST1	Active	801223333	AA123BB	345678	PIPPO
123456	CUST1	Active	801223399	AA126FF	455554	DONALD
123456	CUST1	Definition	801370974	BB123CC	345678	PIPPO
124345	CUST 2	Active	801223433	AA345BC	565555	PLUTO
124345	CUST 2	Active	801223533	AA678CC	987755	TOPOLINO
124345	CUST 2	Active	801371117	AA222RR	121222	MINNIE
124345	CUST 2	Active	801371074	BB567EE	565555	PLUTO
124345	CUST 2	Active	801371174	BB778CC	987755	TOPOLINO

I want to run through the db and when he find a row with same Cust. Id, the same User code, and contract number > to the first one i want to traspose the data. For example i want after the code this situation below:

Cust. Code

Cust Name

Contract state

Contract Number

Plate

User Id

User Name

Cust. Code Rel

Cust Name Rel

Contract state Rel

Contract Num Rel

Plate rel

User ID Rel

User Name Rel

123456

CUST1

Active

801223333

AA123BB

345678

PIPPO

123456

CUST1

Definition

801370974

BB123CC

345678

PIPPO

123456

CUST1

Active

801223399

AA126FF

455554

DONALD

124345

CUST 2

Active

801223433

AA345BC

565555

PLUTO

124345

CUST 2

Active

801371074

BB567EE

565555

PLUTO

124345

CUST 2

Active

801223533

AA678CC

987755

TOPOLINO

124345

CUST 2

Active

801371174

BB778CC

987755

TOPOLINO

124345

CUST 2

Active

801371117

AA222RR

121222

MINNIE

How can i create this second table?

Thanks for the support!

PeterClemmensen · Posted 11-20-2019 05:00 AM

Quick fix:

data want(drop=_:);
   do until (last.DriverCode);
      set temp;
      by CustCode CustName DriverCode;

      if DriverCode=0 then do;
         output;return;
      end;

      if first.DriverCode then do;
         _CustCode=CustCode;
         _CustName=CustName;
         _Contractstate=Contractstate;
         _ContractNumber=ContractNumber;
         _Plate=Plate;
         _DriverCode=DriverCode;
         _DriverName=DriverName;
      end;
      if last.DriverCode then do;
         CustCodeRel=CustCode;
         CustNameRel=CustName;
         ContractStateRel=Contractstate;
         ContractNumRel=ContractNumber;
         PlateRel=Plate;
         DriverCodeRel=DriverCode;
         DriverNameRel=DriverName;
         
         CustCode=_CustCode;
         CustName=_CustName;
         Contractstate=_Contractstate;
         ContractNumber=_ContractNumber;
         Plate=_Plate;
         DriverCode=_DriverCode;
         DriverName=_DriverName;
         if (first.DriverCode=last.DriverCode) then call missing(CustCodeRel,CustNameRel,ContractStateRel,ContractNumRel,PlateRel,DriverCodeRel,DriverNameRel);
         output;
      end;
   end;
run;

The DATA to DATA Step Macro
Blog: SASnrd

View solution in original post

PeterClemmensen · Posted 11-19-2019 11:50 AM

Quick question: Why do you want to do this? Seems to be part of a bigger problem?

Also, are you sure that only two obs matches each other? What if there were three?

The DATA to DATA Step Macro
Blog: SASnrd

FRAFLUTE · Posted 11-19-2019 12:03 PM

I need to do this because the row with the same user id and same customer id are row related and i need to traspone this field for make after some calculation.

PeterClemmensen · Posted 11-19-2019 12:21 PM

Quick n Dirty, but gets the job done

data have;
input CustCode CustName $ Contractstate :$20. ContractNumber Plate $ DriverCode DriverName $;
datalines;
123456 CUST1 Active 801223333 AA123BB 345678 PIPPO
123456 CUST1 Active 801223399 AA126FF 455554 DONALD
123456 CUST1 Definition 801370974 BB123CC 345678 PIPPO
124345 CUST2 Active 801223433 AA345BC 565555 PLUTO
124345 CUST2 Active 801223533 AA678CC 987755 TOPOLINO
124345 CUST2 Active 801371117 AA222RR 121222 MINNIE
124345 CUST2 Active 801371074 BB567EE 565555 PLUTO
124345 CUST2 Active 801371174 BB778CC 987755 TOPOLINO
;

proc sort data=have out=temp;
   by CustCode CustName DriverCode;
run;

data want(drop=_:);
   do until (last.DriverCode);
      set temp;
      by CustCode CustName DriverCode;
      if first.DriverCode then do;
         _CustCode=CustCode;
         _CustName=CustName;
         _Contractstate=Contractstate;
         _ContractNumber=ContractNumber;
         _Plate=Plate;
         _DriverCode=DriverCode;
         _DriverName=DriverName;
      end;
      if last.DriverCode then do;
         CustCodeRel=CustCode;
         CustNameRel=CustName;
         ContractStateRel=Contractstate;
         ContractNumRel=ContractNumber;
         PlateRel=Plate;
         DriverCodeRel=DriverCode;
         DriverNameRel=DriverName;
         
         CustCode=_CustCode;
         CustName=_CustName;
         Contractstate=_Contractstate;
         ContractNumber=_ContractNumber;
         Plate=_Plate;
         DriverCode=_DriverCode;
         DriverName=_DriverName;
         if first.DriverCode=last.DriverCode then call missing(CustCodeRel,CustNameRel,ContractStateRel,ContractNumRel,PlateRel,DriverCodeRel,DriverNameRel);
         output;
      end;
   end;
run;

Result:

CustCode CustName Contractstate ContractNumber Plate   DriverCode DriverName CustCodeRel CustNameRel ContractStateRel ContractNumRel PlateRel DriverCodeRel DriverNameRel 
123456   CUST1    Active        801223333      AA123BB 345678     PIPPO      123456      CUST1       Definition       801370974      BB123CC  345678        PIPPO 
123456   CUST1    Active        801223399      AA126FF 455554     DONALD     .                                        .                       .             
124345   CUST2    Active        801371117      AA222RR 121222     MINNIE     .                                        .                       .             
124345   CUST2    Active        801223433      AA345BC 565555     PLUTO      124345      CUST2       Active           801371074      BB567EE  565555        PLUTO 
124345   CUST2    Active        801223533      AA678CC 987755     TOPOLINO   124345      CUST2       Active           801371174      BB778CC  987755        TOPOLINO

The DATA to DATA Step Macro
Blog: SASnrd

FRAFLUTE · Posted 11-20-2019 04:23 AM

Great!!! It works!!!

But now i've just one problem. There are some Driver Code that have value like 0 where i dont want to traspose anythink. In this case it's possible to insert an "if DriverCode=0" jump to the next Driver Code?

Thank u for the very usefull support!!!

PeterClemmensen · Posted 11-20-2019 04:26 AM

@FRAFLUTE are you able to provide an example of that? Preferably with the 'have' data set that I posted above?

The DATA to DATA Step Macro
Blog: SASnrd

FRAFLUTE · Posted 11-20-2019 04:50 AM

Yes!

data have;
input CustCode CustName $ Contractstate :$20. ContractNumber Plate $ DriverCode DriverName $;
datalines;
123456 CUST1 Active 801223333 AA123BB 345678 PIPPO
123456 CUST1 Active 801223399 AA126FF 455554 DONALD
123456 CUST1 Definition 801370974 BB123CC 345678 PIPPO
124345 CUST2 Active 801223433 AA345BC 565555 PLUTO
124345 CUST2 Active 801223533 AA678CC 987755 TOPOLINO
124345 CUST2 Active 801371117 AA222RR 121222 MINNIE
124345 CUST2 Active 801371074 BB567EE 565555 PLUTO
124345 CUST2 Active 801371174 BB778CC 987755 TOPOLINO
124345 CUST2 Active 801271444 AW888RR 0      . 
124345 CUST2 Active 801277744 BW258JR 0      .
;


proc sort data=have out=temp;
   by CustCode CustName DriverCode;
run;

data want(drop=_:);
   do until (last.DriverCode);
      set temp;
      by CustCode CustName DriverCode;
      if first.DriverCode then do;
         _CustCode=CustCode;
         _CustName=CustName;
         _Contractstate=Contractstate;
         _ContractNumber=ContractNumber;
         _Plate=Plate;
         _DriverCode=DriverCode;
         _DriverName=DriverName;
      end;
      if last.DriverCode then do;
         CustCodeRel=CustCode;
         CustNameRel=CustName;
         ContractStateRel=Contractstate;
         ContractNumRel=ContractNumber;
         PlateRel=Plate;
         DriverCodeRel=DriverCode;
         DriverNameRel=DriverName;
         
         CustCode=_CustCode;
         CustName=_CustName;
         Contractstate=_Contractstate;
         ContractNumber=_ContractNumber;
         Plate=_Plate;
         DriverCode=_DriverCode;
         DriverName=_DriverName;
         if first.DriverCode=last.DriverCode then call missing(CustCodeRel,CustNameRel,ContractStateRel,ContractNumRel,PlateRel,DriverCodeRel,DriverNameRel);
         output;
      end;
   end;
run;

This is the cod whit this situation... in the last 2 row you see that the DriverCode value is 0. In this case, when DriverCode is 0 i want that the code dont make any traspose and the coluon with rel data are blank.

PeterClemmensen · Posted 11-20-2019 05:00 AM

Quick fix:

data want(drop=_:);
   do until (last.DriverCode);
      set temp;
      by CustCode CustName DriverCode;

      if DriverCode=0 then do;
         output;return;
      end;

      if first.DriverCode then do;
         _CustCode=CustCode;
         _CustName=CustName;
         _Contractstate=Contractstate;
         _ContractNumber=ContractNumber;
         _Plate=Plate;
         _DriverCode=DriverCode;
         _DriverName=DriverName;
      end;
      if last.DriverCode then do;
         CustCodeRel=CustCode;
         CustNameRel=CustName;
         ContractStateRel=Contractstate;
         ContractNumRel=ContractNumber;
         PlateRel=Plate;
         DriverCodeRel=DriverCode;
         DriverNameRel=DriverName;
         
         CustCode=_CustCode;
         CustName=_CustName;
         Contractstate=_Contractstate;
         ContractNumber=_ContractNumber;
         Plate=_Plate;
         DriverCode=_DriverCode;
         DriverName=_DriverName;
         if (first.DriverCode=last.DriverCode) then call missing(CustCodeRel,CustNameRel,ContractStateRel,ContractNumRel,PlateRel,DriverCodeRel,DriverNameRel);
         output;
      end;
   end;
run;

The DATA to DATA Step Macro
Blog: SASnrd

FRAFLUTE · Posted 11-20-2019 06:15 AM

Great Job!!!! Thank u so much!!! 🙂

PeterClemmensen · Posted 11-20-2019 06:16 AM

Anytime 🙂

The DATA to DATA Step Macro
Blog: SASnrd

mkeintz · Posted 11-19-2019 12:06 PM

Are the data already grouped by cust. code/cust name/contract number, so that matching records are already contiguous?

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

FRAFLUTE · Posted 11-19-2019 12:15 PM

They are not already grouped but i can group by cust code and user code. The contract code change and the second one is major than the previously.

Thank's!

Ready to join fellow brilliant minds for the SAS Hackathon?