topic Re: Help combining 400+ columns! in New SAS User

Help combining 400+ columns!

mcmaxwell — Fri, 09 Oct 2020 18:23:22 GMT

For my research I gave students a motivation survey at several points throughout the semester to see how motivation changes over time. The survey measures five motivational constructs. My dataset looks like this:

SUID is the identifier, T is the time I gave the survey (SM1, SM2, SM3, and SM4), and SE_A - CLB_A are the motivational constructs measured by the survey.

I would like to make a line graph showing change in motivational constructs over time. To do this, I want to reorganize my data to have the following columns:

SUID T Construct Value

So, for example, each student would have 5 rows for each timepoint (there are four timepoints and five constructs).

I used the following code to try and transpose my data:

/** transpose some of the data **/ 
proc transpose data=all out=transposed name=construct; 
	by notsorted SUID notsorted T; 
	var SE_A TV_A IGO_A EGO_A CLB_A; 
	id SUID; 
run;

and my resulting data looks like this:

Is there a way that I can combine all of the columns labeled with SUID numbers into one column called "Value" without having to type out each SUID number in my code? Or is there a better way to transpose my data to get my desired outcome?

Thank you in advance!

Re: Help combining 400+ columns!

Reeza — Fri, 09 Oct 2020 18:52:27 GMT

What happens if you remove the ID statement entirely?

Re: Help combining 400+ columns!

PGStats — Fri, 09 Oct 2020 18:55:33 GMT

proc transpose data=all out=transposed(rename=col1=value) name=construct; 
	by SUID  T notsorted; 
	var SE_A TV_A IGO_A EGO_A CLB_A; 
run;

Re: Help combining 400+ columns!

ballardw — Fri, 09 Oct 2020 19:27:32 GMT

You are making harder to create any sort of comparison across time graph with this approach.

You would be better off if your "time" variable were some sort of number in all likelihood, even just a integer representing order.

By making a separate variable for each subject/time it just gets harder to make any sort of meaningful graph.

data example;
  input id time measure1 measure2;
datalines;
1 1 25 22
2 1 33 34
3 1 47 49
1 2 22 20
2 2 36 31
3 2 46 49
1 3 28 21
2 3 38 32
3 3 37 39
1 4 21 29
2 4 34 31
3 4 46 41
1 5 29 23
2 5 40 45
3 5 49 43
;

proc sgplot data=example;
 title "All id same measure";
 series x=time y=measure1/group=id;
run;

proc sort data=example;
   by id;
run;

proc sgplot data=example;
 title "By id all measures";
 by id;
 series x=time y=measure1/;
 series x=time y=measure2/;
 yaxis label='Score';

run; title;

Now, manipulate the above example data set the same way and make graphs.