topic Re: Shift table transpose in SAS Programming

Shift table transpose

chinna0369 — Fri, 27 Oct 2023 19:06:03 GMT

Hi,

I am creating a shift table using below code. I have provided the data below. I am transposing 2 times, first transpose is for basecat1 values and my final transpose is for ANAGR2N. But my final transpose if not working I am getting 0 records. Is there any way we can do it in only one transpose or do I need to use different procedure to get counts? I am not sure where I am wrong. Can you please suggest. My expected output is below.

Thanks,

Adi

data have;
infile datalines dsd truncover;
input USUBJID:$30. ANAGR2N:32. PARAM:$200. PARAMCD:$8. AVALCAT1:$200. AVISIT:$200. AVISITN:32. avaln:32. BASECAT1:$200. basen:32.;
datalines4;
TAK-935-3001-09011-105,1,Mobility,EQ5D0201,No problems,V1,1,1,Missing,6
TAK-935-3001-63004-105,1,Mobility,EQ5D0201,No problems,V1,1,1,Missing,6
TAK-935-3001-09011-105,1,Mobility,EQ5D0201,Total,V1,1,9,Missing,6
TAK-935-3001-63004-105,1,Mobility,EQ5D0201,Total,V1,1,9,Missing,6
TAK-935-3001-09011-105,1,Mobility,EQ5D0201,No problems,V1,1,1,Total,9
TAK-935-3001-63004-105,1,Mobility,EQ5D0201,No problems,V1,1,1,Total,9
TAK-935-3001-09011-105,1,Mobility,EQ5D0201,Total,V1,1,9,Total,9
TAK-935-3001-63004-105,1,Mobility,EQ5D0201,Total,V1,1,9,Total,9
TAK-935-3002-02001-202,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-04001-202,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-04003-202,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-04003-203,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-07001-201,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-07001-204,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-09001-203,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-09001-204,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-09002-203,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-09002-204,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-09003-203,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-09003-206,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
;;;;
run;

** Get count;
proc sort data=adqseq1_fin; by paramcd param avisitn avisit anagr2n basen avaln; run;
proc freq data=adqseq1_fin;
	by paramcd param avisitn avisit anagr2n basen avaln;
	tables avalcat1*basecat1 / out=step1;
run;

** Transpose basecat1;
proc sort data=step1; by paramcd param avisitn avisit avaln avalcat1 anagr2n; run; 
proc transpose data=step1(where=(basen ne .)) out=step2(drop=_name_ _label_) prefix=_;
	id basen;
	var count;
	by paramcd param avisitn avisit avaln avalcat1 anagr2n;
run;

** Transpose ANAGR2N;
data step2_;
	set step2;
	if anagr2n=1 then do;
		_11=_1;
		_12=_2;
		_13=_3;
		_14=_4;
		_15=_5;
		_16=_6;
		_19=_9;
	end;
	if anagr2n=2 then do;
		_21=_1;
		_22=_2;
		_23=_3;
		_24=_4;
		_25=_5;
		_26=_6;
		_29=_9;
	end;
	if anagr2n=3 then do;
		_31=_1;
		_32=_2;
		_33=_3;
		_34=_4;
		_35=_5;
		_36=_6;
		_39=_9;
	end;
	drop _1 _2 _3 _4 _5 _6 _9;
run;

** Final traspose for ANAGR2N;
proc sort data=step2_; by paramcd param avisitn avisit avaln avalcat1 _:; run;
proc transpose data=step2_(where=(anagr2n ne .)) out=step3(drop=_name_) prefix=_;
	id anagr2n;
	by paramcd param avisitn avisit avaln avalcat1 _:;
run;

Re: Shift table transpose

PaigeMiller — Fri, 27 Oct 2023 17:54:47 GMT

Please provide data as working SAS data step code (examples and instructions), as you did in earlier threads. We cannot work with data in screen captures. We cannot work with data in Excel. It must be in the form stated.

When something doesn't work, please provide us with the LOG for your code. We need to see the ENTIRE log for the step that has the error (the ENTIRE log for the step that has the error, not just the error messages). Copy the log as text and paste it into the window that appears when you click on the </> icon.

Re: Shift table transpose

chinna0369 — Fri, 27 Oct 2023 18:19:18 GMT

Hi,

Please find the data below.

data have;
infile datalines dsd truncover;
input USUBJID:$30. ANAGR2N:32. PARAM:$200. PARAMCD:$8. AVALCAT1:$200. AVISIT:$200. AVISITN:32. avaln:32. BASECAT1:$200. basen:32.;
datalines4;
TAK-935-3001-09011-105,1,Mobility,EQ5D0201,No problems,V1,1,1,Missing,6
TAK-935-3001-63004-105,1,Mobility,EQ5D0201,No problems,V1,1,1,Missing,6
TAK-935-3001-09011-105,1,Mobility,EQ5D0201,Total,V1,1,9,Missing,6
TAK-935-3001-63004-105,1,Mobility,EQ5D0201,Total,V1,1,9,Missing,6
TAK-935-3001-09011-105,1,Mobility,EQ5D0201,No problems,V1,1,1,Total,9
TAK-935-3001-63004-105,1,Mobility,EQ5D0201,No problems,V1,1,1,Total,9
TAK-935-3001-09011-105,1,Mobility,EQ5D0201,Total,V1,1,9,Total,9
TAK-935-3001-63004-105,1,Mobility,EQ5D0201,Total,V1,1,9,Total,9
TAK-935-3002-02001-202,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-04001-202,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-04003-202,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-04003-203,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-07001-201,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-07001-204,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-09001-203,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-09001-204,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-09002-203,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-09002-204,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-09003-203,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
TAK-935-3002-09003-206,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1
;;;;
run;

And here is the log for my above code, there is no error, I am just getting 0 records.

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
INFO: Character variables have defaulted to a length of 200 at the places given by: (Line):(Column). Truncation can result.
40:1 RC
72
73 data adqseq1_fin;
74 set adqseq1_fin3;
75 output;
76 anagr2="Total";
77 anagr2n=3;
78 output;
79 run;

NOTE: There were 7,680 observations read from the data set WORK.ADQSEQ1_FIN3.
NOTE: The data set WORK.ADQSEQ1_FIN has 15,360 observations and 17 variables.
NOTE: Compressing data set WORK.ADQSEQ1_FIN decreased size by 87.01 percent.
Compressed is 40 pages; un-compressed would require 308 pages.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
user cpu time 0.02 seconds
system cpu time 0.00 seconds
memory 997.03k
OS Memory 55204.00k
Timestamp 10/27/2023 06:17:42 PM
Step Count 861 Switch Count 5
Page Faults 0
Page Reclaims 19
Page Swaps 0
Voluntary Context Switches 19
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 5128

80 proc sort nodupkey; by studyid usubjid paramcd avisitn adt anagr2n avaln basen; run;

NOTE: There were 15,360 observations read from the data set WORK.ADQSEQ1_FIN.
NOTE: SAS sort was used.
NOTE: 0 observations with duplicate key values were deleted.
NOTE: The data set WORK.ADQSEQ1_FIN has 15,360 observations and 17 variables.
NOTE: Compressing data set WORK.ADQSEQ1_FIN decreased size by 87.01 percent.
Compressed is 40 pages; un-compressed would require 308 pages.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.04 seconds
user cpu time 0.03 seconds
system cpu time 0.02 seconds
memory 25824.18k
OS Memory 79884.00k
Timestamp 10/27/2023 06:17:42 PM
Step Count 862 Switch Count 5
Page Faults 0
Page Reclaims 6004
Page Swaps 0
Voluntary Context Switches 17
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 5128

81
82 ** Get count;
83 proc sort data=adqseq1_fin; by paramcd param avisitn avisit anagr2n basen avaln; run;

NOTE: There were 15,360 observations read from the data set WORK.ADQSEQ1_FIN.
NOTE: SAS sort was used.
NOTE: The data set WORK.ADQSEQ1_FIN has 15,360 observations and 17 variables.
NOTE: Compressing data set WORK.ADQSEQ1_FIN decreased size by 87.01 percent.
Compressed is 40 pages; un-compressed would require 308 pages.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.05 seconds
user cpu time 0.03 seconds
system cpu time 0.02 seconds
memory 28130.15k
OS Memory 81940.00k
Timestamp 10/27/2023 06:17:42 PM
Step Count 863 Switch Count 5
Page Faults 0
Page Reclaims 6561
Page Swaps 0
Voluntary Context Switches 18
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 5128

84 proc freq data=adqseq1_fin;
85 by paramcd param avisitn avisit anagr2n basen avaln;
86 tables avalcat1*basecat1 / out=step1;
87 run;

NOTE: BY line has been truncated at least once.
NOTE: The above message was for the following BY group:
Parameter Code=EQ5D0203 Parameter=Usual Activities Analysis Visit (N)=5 Analysis Visit=V5 Week 26 Analysis Group 2 (N)=1
basen=6
NOTE: There were 15,360 observations read from the data set WORK.ADQSEQ1_FIN.
NOTE: The data set WORK.STEP1 has 979 observations and 11 variables.
NOTE: Compressing data set WORK.STEP1 decreased size by 76.92 percent.
Compressed is 3 pages; un-compressed would require 13 pages.
NOTE: PROCEDURE FREQ used (Total process time):
real time 11.45 seconds
user cpu time 11.17 seconds
system cpu time 0.08 seconds
memory 20049.71k
OS Memory 69828.00k
Timestamp 10/27/2023 06:17:54 PM
Step Count 864 Switch Count 240
Page Faults 0
Page Reclaims 3580
Page Swaps 0
Voluntary Context Switches 720
Involuntary Context Switches 34
Block Input Operations 1488
Block Output Operations 7944

88
89 ** Transpose basecat1;
90 proc sort data=step1; by paramcd param avisitn avisit avaln avalcat1 anagr2n; run;

NOTE: There were 979 observations read from the data set WORK.STEP1.
NOTE: SAS sort was used.
NOTE: The data set WORK.STEP1 has 979 observations and 11 variables.
NOTE: Compressing data set WORK.STEP1 decreased size by 76.92 percent.
Compressed is 3 pages; un-compressed would require 13 pages.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 2875.75k
OS Memory 68820.00k
Timestamp 10/27/2023 06:17:54 PM
Step Count 865 Switch Count 11
Page Faults 0
Page Reclaims 424
Page Swaps 0
Voluntary Context Switches 42
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 392

91 proc transpose data=step1(where=(basen ne .)) out=step2(drop=_name_ _label_) prefix=_;
92 id basen;
93 var count;
94 by paramcd param avisitn avisit avaln avalcat1 anagr2n;
95 run;

NOTE: There were 781 observations read from the data set WORK.STEP1.
WHERE basen not = .;
NOTE: The data set WORK.STEP2 has 251 observations and 14 variables.
NOTE: Compressing data set WORK.STEP2 decreased size by 33.33 percent.
Compressed is 2 pages; un-compressed would require 3 pages.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 2837.59k
OS Memory 68552.00k
Timestamp 10/27/2023 06:17:54 PM
Step Count 866 Switch Count 19
Page Faults 0
Page Reclaims 149
Page Swaps 0
Voluntary Context Switches 72
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 408

96
97 ** Transpose ANAGR2N;
98 data step2_;
99 set step2;
100 if anagr2n=1 then do;
101 _11=_1;
102 _12=_2;
103 _13=_3;
104 _14=_4;
105 _15=_5;
106 _16=_6;
107 _19=_9;
108 end;
109 if anagr2n=2 then do;
110 _21=_1;
111 _22=_2;
112 _23=_3;
113 _24=_4;
114 _25=_5;
115 _26=_6;
116 _29=_9;
117 end;
118 if anagr2n=3 then do;
119 _31=_1;
120 _32=_2;
121 _33=_3;
122 _34=_4;
123 _35=_5;
124 _36=_6;
125 _39=_9;
126 end;
127 drop _1 _2 _3 _4 _5 _6 _9;
128 run;

NOTE: There were 251 observations read from the data set WORK.STEP2.
NOTE: The data set WORK.STEP2_ has 251 observations and 28 variables.
NOTE: Compressing data set WORK.STEP2_ decreased size by 50.00 percent.
Compressed is 2 pages; un-compressed would require 4 pages.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 898.81k
OS Memory 67012.00k
Timestamp 10/27/2023 06:17:54 PM
Step Count 867 Switch Count 4
Page Faults 0
Page Reclaims 62
Page Swaps 0
Voluntary Context Switches 25
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 264

129
130 ** Final traspose for ANAGR2N;
131 proc sort data=step2_; by paramcd param avisitn avisit avaln avalcat1 _:; run;

NOTE: There were 251 observations read from the data set WORK.STEP2_.
NOTE: SAS sort was used.
NOTE: The data set WORK.STEP2_ has 251 observations and 28 variables.
NOTE: Compressing data set WORK.STEP2_ decreased size by 50.00 percent.
Compressed is 2 pages; un-compressed would require 4 pages.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.01 seconds
memory 1821.68k
OS Memory 68048.00k
Timestamp 10/27/2023 06:17:54 PM
Step Count 868 Switch Count 4
Page Faults 0
Page Reclaims 123
Page Swaps 0
Voluntary Context Switches 21
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 264

132 proc transpose data=step2_(where=(anagr2n ne .)) out=step3(drop=_name_) prefix=_;
133 id anagr2n;
134 by paramcd param avisitn avisit avaln avalcat1 _:;
135 run;

NOTE: No variables to transpose.
NOTE: There were 251 observations read from the data set WORK.STEP2_.
WHERE anagr2n not = .;
NOTE: The data set WORK.STEP3 has 0 observations and 30 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 1036.25k
OS Memory 67268.00k
Timestamp 10/27/2023 06:17:54 PM
Step Count 869 Switch Count 7
Page Faults 0
Page Reclaims 92
Page Swaps 0
Voluntary Context Switches 31
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 272

Re: Shift table transpose

PaigeMiller — Fri, 27 Oct 2023 18:35:38 GMT

Maxim 2: Read the log

132 proc transpose data=step2_(where=(anagr2n ne .)) out=step3(drop=_name_) prefix=_;
133 id anagr2n;
134 by paramcd param avisitn avisit avaln avalcat1 _:;
135 run;

NOTE: No variables to transpose.

You can't do a transpose if there are no variables to transpose. Why are there no variables to transpose? Because you told PROC TRANSPOSE that every variable in the data set is either an ID variable for PROC TRANSPOSE, or it is a BY variable for PROC TRANSPOSE, and so there's no variables left to transpose.

Re: Shift table transpose

ballardw — Fri, 27 Oct 2023 19:01:57 GMT

Can you show what the result is supposed to look like? Code that does not run or doesn't provide an output does not describe what the desired result may look like.

And are you sure that you need a data set? Sometime a report is more likely to generate output in a complex appearance.

Re: Shift table transpose

chinna0369 — Fri, 27 Oct 2023 19:15:15 GMT

I have added expected output now in my post. Thanks!

Re: Shift table transpose

mkeintz — Sun, 29 Oct 2023 18:27:45 GMT

It appears to me that you can do most of this in PROC TABULATE, and I imagine all of it in PROC REPORT.

I can offer a PROC TABULATE solution, but others will have to do the PROC REPORT.

But first, you have instances of "Total" for BASECAT1 and AVALCAT1. You haven't said what you want to do with them.

Re: Shift table transpose

PaigeMiller — Sun, 29 Oct 2023 18:32:28 GMT

You can use the %TABLEN macro to produce such a table.