BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Lohia
Calcite | Level 5

Hi Guys,

 

Could you please help me to build a logic? Test scenario given below:

 

Data set contains values:

C1 a   a   b  c d d

V1 10 12 2 4 5 7

C2 s s f f e e

V2 2 3 4 5 6 7 

C3 q q w d e e e e

V3 2 5 6 7 8 8 8 8

C4 f f f f f f g h j k 

V4 3 4 5 6 7 6 7 8 8  6 

 

Result i am looking for :

C1 a b c  d

V1 22 2 4 12

C2 s f e

V2 5 9 13

C3 q w d e

V3 7 6 7 32

C4 f g h j k

V4 31 76 7 8 8 6

 

Basicaly, i want to reduce the row sequence by adding consecutives values and make it one.

 

Please provide your comments if possible.

 

Thanks!

Parveen

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

Like this?

 


data HAVE;
infile cards missover;
input (ID VAL1-VAL8) ($) ;
cards;
C1 a  a  b  c d d
V1 10 12 2 4 5 7
C2 s s f f e e
V2 2 3 4 5 6 7 
C3 q q w d e e e e
V3 2 5 6 7 8 8 8 8
C4 f f f f f f g h  
V4 3 4 5 6 7 6 7 8  
run;

 
data WANT;
  merge HAVE(rename=(ID=IDC VAL1=VALC1 VAL2=VALC2 VAL3=VALC3 VAL4=VALC4 VAL5=VALC5 VAL6=VALC6 VAL7=VALC7 VAL8=VALC8) 
             where=(IDC=:'C'))
        HAVE(rename=(ID=IDN VAL1=VALN1 VAL2=VALN2 VAL3=VALN3 VAL4=VALN4 VAL5=VALN5 VAL6=VALN6 VAL7=VALN7 VAL8=VALN8) 
             where=(IDN=:'V'));
  array VAL   [8] $;
  array VALC  [8] $;
  array VALN  [8] $;
  I=2;
  do while(VALC[I] ne ' ');
    if VALC[I]=VALC[I-1] then do;
      VALN[I-1] =cat(input(VALN[I],9.) + input(VALN[I-1],9.));
      do J=I to 7;
        VALC[J] =VALC[J+1];
        VALN[J] =VALN[J+1];
      end;
      VALC[8] =' ';
      VALN[8] =' ';
    end;
    else I+1;
  end;
  ID=IDC;
  do I=1 to 8;
    VAL[I]=VALC[I];
  end;
  output ;
  ID=IDN;
  do I=1 to 8;
    VAL[I]=VALN[I];
  end;
  output;
  keep ID VAL1-VAL8;
run;
proc print; 
  var ID VAL1-VAL8;
run;

 

 

 

Obs ID VAL1 VAL2 VAL3 VAL4 VAL5 VAL6 VAL7 VAL8
1 C1 a b c d        
2 V1 22 2 4 12        
3 C2 s f e          
4 V2 5 9 13          
5 C3 q w d e        
6 V3 7 6 7 32        
7 C4 f g h          
8 V4 31 7 8          

View solution in original post

6 REPLIES 6
LinusH
Tourmaline | Level 20
For the chars I think I get the logic.
Transpose to long and simply do select distinct/proc sort NODUPKEY.
The logic for the numbers I don't get. Please elaborate.
Data never sleeps
Lohia
Calcite | Level 5

Hi LinusH,

 

In this data , two rows works in pair.

Like first pair: [C1] a a b c d d [V1] 10 12 2 4 5 7 Result: [C1] a b c d [V1] 22 2 4 12 You can see here, a repeated two times, so it becomes one and in same time a's values are also added in next row.

 

C1 and V1 are two rows in which C1 having chars and v1 contains char values.

 

I tihnk , you can get it (i tried to explain  🙂 )

Thanks!

 

ChrisNZ
Tourmaline | Level 20

Like this?

 


data HAVE;
infile cards missover;
input (ID VAL1-VAL8) ($) ;
cards;
C1 a  a  b  c d d
V1 10 12 2 4 5 7
C2 s s f f e e
V2 2 3 4 5 6 7 
C3 q q w d e e e e
V3 2 5 6 7 8 8 8 8
C4 f f f f f f g h  
V4 3 4 5 6 7 6 7 8  
run;

 
data WANT;
  merge HAVE(rename=(ID=IDC VAL1=VALC1 VAL2=VALC2 VAL3=VALC3 VAL4=VALC4 VAL5=VALC5 VAL6=VALC6 VAL7=VALC7 VAL8=VALC8) 
             where=(IDC=:'C'))
        HAVE(rename=(ID=IDN VAL1=VALN1 VAL2=VALN2 VAL3=VALN3 VAL4=VALN4 VAL5=VALN5 VAL6=VALN6 VAL7=VALN7 VAL8=VALN8) 
             where=(IDN=:'V'));
  array VAL   [8] $;
  array VALC  [8] $;
  array VALN  [8] $;
  I=2;
  do while(VALC[I] ne ' ');
    if VALC[I]=VALC[I-1] then do;
      VALN[I-1] =cat(input(VALN[I],9.) + input(VALN[I-1],9.));
      do J=I to 7;
        VALC[J] =VALC[J+1];
        VALN[J] =VALN[J+1];
      end;
      VALC[8] =' ';
      VALN[8] =' ';
    end;
    else I+1;
  end;
  ID=IDC;
  do I=1 to 8;
    VAL[I]=VALC[I];
  end;
  output ;
  ID=IDN;
  do I=1 to 8;
    VAL[I]=VALN[I];
  end;
  output;
  keep ID VAL1-VAL8;
run;
proc print; 
  var ID VAL1-VAL8;
run;

 

 

 

Obs ID VAL1 VAL2 VAL3 VAL4 VAL5 VAL6 VAL7 VAL8
1 C1 a b c d        
2 V1 22 2 4 12        
3 C2 s f e          
4 V2 5 9 13          
5 C3 q w d e        
6 V3 7 6 7 32        
7 C4 f g h          
8 V4 31 7 8          
Lohia
Calcite | Level 5
Hi ChrisNZ, Thanks a lot for the solution. I ran this logic on 61 variables and 500 observations . You rock 🙂 .
jklaverstijn
Rhodochrosite | Level 12

Hi @Lohia if this works so well for you, please consider accepting this as a solution, giving @ChrisNZ the forum credits he deserves. Smiley Happy

Ksharp
Super User
data HAVE;
infile cards missover;
input (ID VAL1-VAL8) ($) ;
cards;
C1 a  a  b  c d d
V1 10 12 2 4 5 7
C2 s s f f e e
V2 2 3 4 5 6 7 
C3 q q w d e e e e
V3 2 5 6 7 8 8 8 8
C4 f f f f f f g h  
V4 3 4 5 6 7 6 7 8  
run;
proc transpose data=have out=temp(drop=_name_);
by id notsorted;
var val:;
run;
data temp1;
 merge temp(where=(id=:'C' and col1 is not missing))
       temp(where=(_id=:'V' and _col1 is not missing) rename=(id=_id col1=_col1) );
run;
data temp2;
 set temp1;
 by id col1 notsorted;
 if first.col1 then sum=0;
 sum+input(_col1,best8.);      
 if last.col1;
run;
proc transpose data=temp2 out=temp3;
 by id _id notsorted;
 var col1 sum;
run;
data want;
 set temp3;
 length new_id $ 40;
 new_id=ifc(_name_='COL1',id,_id);
 drop id _id _name_;
run;





sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1051 views
  • 0 likes
  • 5 in conversation