topic Re: Proc Transpose in SAS Data Management

Proc Transpose

emaneman — Tue, 17 Sep 2019 05:12:49 GMT

Dear all,

I wonder whether you can help me with this issue.

In an experiment I have several groups of three participants each, with defined roles as D, M and O, answering questions about each other Subsequently, each participant evaluated the other two on variable Y, using a scale 0-100. The data looks like in the attached excel file.

ID= participant N

Group=group N

Role=Role (D, M o O)

V1me=self evaluation

V1D=evaluation of the D within the group

V1M=evaluation of the M within the group

V1O=evaluation of the O within the group

If the participant has Role D, then V1D has missing value, since the participant did not evaluate the D, but evaluated the self, M and O. Same for the other Role(s).

I need to compute a measure of accuracy, by comparing the evaluation that one gives of the other two members within the group, to the evaluation that they give to themselves. For instance, if the participant is a D, then his/her D_accuracy missing, his M_accuracy it is the difference between his/her evaluation of M and the evaluation that M gives to himself/herself, and for O_accuracy is the difference between his/her evaluation of O and the evaluation that O gives to himself/herself.

In the data sample attached, the D accuracy for subject 1004 is missing value, for M it is 10 (10-0) and for O it is 0 (0-0).

I would like SAS to do this for me, so that three variables are ultimately added for each participants, indicating D_accuracy, M_accuracy and O-accuracy.

Any suggestion would be welcome!

Thanks in advance.

group

Role

V1me

V1D

V1M

V1O

Group

Re: Proc Transpose

VinitvictorCorr — Thu, 19 Sep 2019 06:27:28 GMT

can you show what the source data would look like?

Re: Proc Transpose

emaneman — Thu, 19 Sep 2019 14:03:57 GMT

Hello VinitvictorCorr,

The source data file should be attached to the original posting of mine, but I include it here, too.

This source data is only a sample of the entire file, with two groups (variable "group"of three people each.

Thanks for considering to look into it!

Re: Proc Transpose

VinitvictorCorr — Mon, 23 Sep 2019 03:51:48 GMT

OK,, i thought of it as a result.. so you want the results of v1d,v1m,v1o to be in one column ? if it is else-wise, could you please show me how the end result would look like? I don't want to provide you with a wrong code.

Re: Proc Transpose

emaneman — Mon, 23 Sep 2019 10:37:54 GMT

hello again.

I added the desired outcome in the attached file, which previously contained only the original format.

You can see the formula in excel that I computed to obtained the desired values.

Thank you very much for your attention.

Re: Proc Transpose

VinitvictorCorr — Tue, 24 Sep 2019 05:49:21 GMT

Heres your program. Its a little big and not alligned well :p, but it should do the trick:-

DATA WORK.trial1;
LENGTH
ID 8
Group 8
Role $ 1
V1me 8
V1D 8
V1M 8
V1O 8 ;
FORMAT
ID BEST12.
Group BEST12.
Role $CHAR1.
V1me BEST12.
V1D BEST12.
V1M BEST12.
V1O BEST12. ;
INFORMAT
ID BEST12.
Group BEST12.
Role $CHAR1.
V1me BEST12.
V1D BEST12.
V1M BEST12.
V1O BEST12. ;
INFILE "please put your path\filename here"'
LRECL=18
ENCODING="WLATIN1"
TERMSTR=CRLF
DLM='7F'x

MISSOVER
DSD ;
INPUT
ID : BEST32.
Group : BEST32.
Role : $CHAR1.
V1me : BEST32.
V1D : BEST32.
V1M : BEST32.
V1O : BEST32. ;
RUN;

DATA K/*(KEEP=ID GROUP ROLE V1ME)*/;
SET TRIAL1;
RUN;

proc sql;
select id,count(id) into :idvars separated by " _num_",:cnt from k;
quit;

PROC SORT DATA=K(rename=(v1d=d v1m=m v1o=o));BY id;RUN;

PROC TRANSPOSE DATA=K OUT=L name=role prefix=_num_;
BY group;
ID id;
VAR d m o;
RUN;
data l;
set l;
role=upcase(role);

data c(keep=role v1me);
set k;
run;
proc sort data=c;by role;
proc sort data=l;by role;
data s;
merge l(in=a) c;
by role;
if a;
run;

%macro count;

%do i=1 %to &cnt.;
data s;
set s;
%scan(_num_&idvars.,&i.)=%scan(_num_&idvars.,&i.)-v1me;
run;
%end;

%mend count;
%count;

Proc sort data=s;by group ;

proc transpose data=s out=a name=ida;
by group ;
id role;
var _num_&idvars.;
run;

data b(drop=ida);
set a;
id=input(compress(ida,"_num_"),best.);
run;

proc sort data=k out=met(keep=id group);by id group;
proc sort data=b;by id group;

data t(rename=(D=D_accuracy M=M_Accuracy O=O_Accuracy));
merge met(in=a) b;
by id group;
if a;
run;

the input dataset is trial1 and output dataset is t.

Note:- please change the location in the infile statement. Also let me know if you need any changes.

The output looks like below attached screenshot

Re: Proc Transpose

emaneman — Wed, 25 Sep 2019 09:02:24 GMT

Hello again!

Thanks for all this effort and code! I am having only one issue with the

INFILE. I substitute with the path to the CSV file, but it doesn't like that.

Re: Proc Transpose

VinitvictorCorr — Wed, 25 Sep 2019 09:09:38 GMT

if you are using sas, direct import it using import tab in file and dont use the first part of the code

Re: Proc Transpose

emaneman — Wed, 25 Sep 2019 23:22:59 GMT

thank you, i will try that.