creating new table

[Nini1]

Hi everyone,

I have such data

and want to write code that will give me this table.

I tried some but did not work. Could you please help me to write the code that will give me this result?

Thank you

[ballardw]

You need to describe what the process is to have a chance.

For example, how does Var1_2 or Var1_3 get a value of "2" for Id=1 when none of the values shown for Id 1 in the "have" data have a value of 2.

Please post data as text, preferably as data step code. We can't code from pictures.

[Nini1]

sorry, I missed this in description. Var1_1 is Var1's first row, Var1_2 is summing var 1's first and second rows and so on for each client. the same for other variables

[sbxkoenk]

Hello,

If you would have provided the community members with your data using a datastep with datalines (instead of a screenshot), you would have got tons of solutions meanwhile.

I quickly assembled something using invented data.

The code is not generic at all!!

Ideally you query your available data for the max n° of rows for an id and you wrap everything in a macro (with a do-loop) to avoid doing 10 times the same thing.  

Anyway, it's a weird transpose. I couldn't rapidly fix it with a proc transpose. But I guess that's because of lack of time (on my side). proc transpose can definitely do this.

Let me know if you cannot make this code more dense and generic by using a macro and I will do it for you (but not today, I need to go for dinner 😉).

data have;
 input id var1 var2 var3;
 cards;
3 0 1 2
3 1 2 1
3 4 8 1
4 0 1 2
4 1 2 1
4 4 8 1
;
run;

proc expand data=have out=have_expand;
 by id;
 convert var1 / transform=(CUSUM);
 convert var2 / transform=(CUSUM);
 convert var3 / transform=(CUSUM);
run;

data have_expand; set have_expand; time=time+1; run;
data time1(rename=(var1=var1_1 var2=var2_1 var3=var3_1)); set have_expand; where time=1; run;
data time2(rename=(var1=var1_2 var2=var2_2 var3=var3_2)); set have_expand; where time=2; run;
data time3(rename=(var1=var1_3 var2=var2_3 var3=var3_3)); set have_expand; where time=3; run;

data want(drop=time);
 merge time1 time2 time3;
run;
/* end of program */

Cheers,

Koen

[sbxkoenk]

Also, I guess the real variable names are not var1, var2 and var3. If you have 1000+ variables with non-common names (possibly up to 32 chars for the name), you definitely need to transform this into more generic code. I can do that tomorrow if you cannot sort it out.
Koen

[PaigeMiller]

I'm wondering what the benefit of this transformation is. Generally, such a transformation, where an sequence number is placed into a variable name, is not recommended because future use of this data set would proceed more easily without such a transformation.

This seems like the X-Y problem, where someone wants to accomplish something, and then decides somewhere along the way they have to perform a task, when in reality that task doesn't need to be performed, there are better and easier paths to the end result.

Could you tell us what you want to do with this data after the transformation is performed? Also, regarding the output you show, do you want this to be a table that can be included in an output document, or do you want this to be a data set for further analysis?

[Nini1]

Hi everyone,

I have such data

and want to write code that will give me this table.

For first person var1_1's first entry equals to var1's first entry.var1_2's entry equals to var1's first and second entries (1+1) ,var1_3's entry equals 1+1+0. It's the same logic for other variables and other persons. 

I tried the code below but did not work.

Proc sort data = data have;by id;run;
Data data_want_1;
do until (last.id);
set data have;
by id;

If First.id then do;
var1_=First.var1;
end;

 if var1=1 then var1_=var1+1;else var1_=var1+0;

Array A_var1_{*} var1_1-var1_3;
A_var1_{Count} =var1_;end;

Could you please help me to write the code that will give me this result?

Thank you

[AnnaBrown]

Hi @Nini1 ,

Thanks for asking your questions in the SAS Communities! It looks like this question is the same one you posted in "Creating new table," but with more details. Can you add a comment to the original post and include these additional details? That way everyone following that post will be aware. After you've done that, feel free to delete this post.

Thanks!

Anna

[Reeza]

These seem like running totals. Any particular reason you need to flip the data to do it? What are you planning to do next? I suspect there are many ways to simplify this process.

[andreas_lds]

@Nini1 : i have merged your posts.

[Ksharp]

The simplest way is using proc summary.

data have;
 input id var1 var2 var3;
 cards;
3 0 1 2
3 1 2 1
3 4 8 1
4 0 1 2
4 1 2 1
4 4 8 1
;
run;
proc sql noprint;
select max(n) into :n
 from (select count(*) as n from have group by id);
quit;
proc summary data=have;
by id;
output out=want(drop=_type_ _freq_) idgroup(out[&n] (var1-var3)=);
run;

[sbxkoenk]

Great! I hadn't thought about that solution. It has been a long time since I have used the <<idgroup>> in the output statement of PROC SUMMARY (or PROC MEANS).
Thanks,
Koen

[Ksharp]

Another two simple ways are
1) Double PROC TRANSPOSE
2) MERGE Skill . I submit a paper to talk about this skill:
https://support.sas.com/resources/papers/proceedings15/2785-2015.pdf