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

data one;
input ID $ number ;
datalines;
a1 100
a1 150
a1 200
a2 125
a2 150
a2 300
a2 400

;
run;

 

Can anyone help me how can I get the  below output .

 

Output : 

 

ID number  c1   c2   c3
a1 100        .  
a1 150     100
a1 200     150 100
a2 125      .
a2 150    125
a2 300    150 125
a2 400    300 150 125

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Determine the number of needed variables first, and then use array processing:

data one;
input ID $ number ;
datalines;
a1 100
a1 150
a1 200
a2 125
a2 150
a2 300
a2 400
a3 3
a3 4
a3 5
a3 6
a3 7
a3 8
a3 9
;
run;

proc sql noprint;
select max(counter) - 1 into :counter from (select count(*) as counter from one group by id);
quit;

%let counter=&counter; *removes blanks;

data want;
set one;
by id;
array targets {&counter} c1-c&counter;
retain c1-c&counter;
if first.id
then do;
  i = 1;
  do i1 = 1 to &counter;
    targets{i1} = .;
  end;
end;
else i + 1;
do i1 = i-1 to 2 by -1;
  targets{i1} = targets{i1-1};
end;
targets{1} = lag(number);
if first.id then targets{1} = .;
drop i i1;
run;

I added your second data example as group "a3".

View solution in original post

6 REPLIES 6
RM6
Obsidian | Level 7 RM6
Obsidian | Level 7
this can be achieved by using lag function and first.
some thing like this


data two;
set one;
by id;
c1=lag(number);
if first.id then c1=.;
run;
proc sort data=two; by id number c1;run;
data two;
set two;
by id number c1;
c2=lag(c1);
if first.id then c2=.;
run;
proc sort data=two; by id number c1 c2;run;
data two;
set two;
by id number c1 c2;
c3=lag(c2);
if first.id then c3=.;
run;
Manikanta
Calcite | Level 5
Thanks for your reply .
But what if we have more data .
For example :
a1 3
a1 4
a1 5
a1 6
a1 7
a1 8
a1 9.
.
.
.
N

Kurt_Bremser
Super User

Determine the number of needed variables first, and then use array processing:

data one;
input ID $ number ;
datalines;
a1 100
a1 150
a1 200
a2 125
a2 150
a2 300
a2 400
a3 3
a3 4
a3 5
a3 6
a3 7
a3 8
a3 9
;
run;

proc sql noprint;
select max(counter) - 1 into :counter from (select count(*) as counter from one group by id);
quit;

%let counter=&counter; *removes blanks;

data want;
set one;
by id;
array targets {&counter} c1-c&counter;
retain c1-c&counter;
if first.id
then do;
  i = 1;
  do i1 = 1 to &counter;
    targets{i1} = .;
  end;
end;
else i + 1;
do i1 = i-1 to 2 by -1;
  targets{i1} = targets{i1-1};
end;
targets{1} = lag(number);
if first.id then targets{1} = .;
drop i i1;
run;

I added your second data example as group "a3".

Manikanta
Calcite | Level 5
Many Thanks.
Ksharp
Super User
data one;
input ID $ number ;
datalines;
a1 100
a1 150
a1 200
a2 125
a2 150
a2 300
a2 400
;
run;

data want;
 set one;
 lag=lag(number);
 lag2=lag2(number);
 if id ne lag(id) then call missing(lag);
 if id ne lag2(id) then call missing(lag2);
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
  • 1979 views
  • 1 like
  • 4 in conversation