https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362120#M64516 <P>Thanks a lot Art; this is the log:</P><P>&nbsp;</P><P>972&nbsp; %macro transpose(libname_in=,<BR />973&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; libname_out=,<BR />974&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; data=,<BR />975&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; out=,<BR />976&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; by=,<BR />977&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; prefix=,<BR />978&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; var=,<BR />979&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; autovars=,<BR />980&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; id=,<BR />981&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; descendingid=,<BR />982&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; var_first=,<BR />983&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; format=,<BR />984&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; delimiter=,<BR />985&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; copy=,<BR />986&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; drop=,<BR />987&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sort=,<BR />988&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sort_options=,<BR />989&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; use_varname=,<BR />990&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; preloadfmt=,<BR />991&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; guessingrows=,<BR />992&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; newid=);<BR />993<BR />994&nbsp; /*Check whether the data and out parameters contain one or two-level filenames*/<BR />995&nbsp;&nbsp;&nbsp; %let lp=%sysfunc(findc(%superq(data),%str(%()));<BR />996&nbsp;&nbsp;&nbsp; %if &amp;lp. %then %do;<BR />997&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let rp=%sysfunc(findc(%superq(data),%str(%)),b));<BR />998&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let dsoptions=%qsysfunc(substrn(%nrstr(%superq(data)),&amp;lp+1,&amp;rp-&amp;lp-1));<BR />999&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let data=%sysfunc(substrn(%nrstr(%superq(data)),1,%eval(&amp;lp-1)));<BR />1000&nbsp;&nbsp;&nbsp; %end;<BR />1001&nbsp;&nbsp;&nbsp; %else %let dsoptions=;<BR />1002&nbsp;&nbsp;&nbsp; %if %sysfunc(countw(&amp;data.)) eq 2 %then %do;<BR />1003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let libname_in=%scan(&amp;data.,1);<BR />1004&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let data=%scan(&amp;data.,2);<BR />1005&nbsp;&nbsp;&nbsp; %end;<BR />1006&nbsp;&nbsp;&nbsp; %else %if %length(&amp;libname_in.) eq 0 %then %do;<BR />1007&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let libname_in=work;<BR />1008&nbsp;&nbsp;&nbsp; %end;<BR />1009<BR />1010&nbsp;&nbsp;&nbsp; %if %sysfunc(countw(&amp;out.)) eq 2 %then %do;<BR />1011&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let libname_out=%scan(&amp;out.,1);<BR />1012&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let out=%scan(&amp;out.,2);<BR />1013&nbsp;&nbsp;&nbsp; %end;<BR />1014&nbsp;&nbsp;&nbsp; %else %if %length(&amp;libname_out.) eq 0 %then %do;<BR />1015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let libname_out=work;<BR />1016&nbsp;&nbsp;&nbsp; %end;<BR />1017<BR />1018&nbsp;&nbsp;&nbsp; %if %length(&amp;newid.) eq 0 %then %do;<BR />1019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let newid=row;<BR />1020&nbsp;&nbsp;&nbsp; %end;<BR />1021<BR />1022&nbsp;&nbsp;&nbsp; /*obtain last by variable*/<BR />1023&nbsp;&nbsp;&nbsp; %if %length(&amp;by.) gt 0 %then %do;<BR />1024&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let lastby=%scan(&amp;by.,-1);<BR />1025&nbsp;&nbsp;&nbsp; %end;<BR />1026&nbsp;&nbsp;&nbsp; %else %do;<BR />1027&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let lastby=;<BR />1028&nbsp;&nbsp;&nbsp; %end;<BR />1029<BR />1030&nbsp; /*Create macro variable to contain a list of variables to be copied*/<BR />1031&nbsp;&nbsp; %let to_copy=;<BR />1032&nbsp;&nbsp;&nbsp; %if %length(&amp;copy.) gt 0 %then %do;<BR />1033&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; data t_e_m_p;<BR />1034&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set &amp;libname_in..&amp;data. (obs=1 keep=&amp;copy.);<BR />1035&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; run;<BR />1036<BR />1037&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; proc sql noprint;<BR />1038&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select name<BR />1039&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :to_copy separated by " "<BR />1040&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from dictionary.columns<BR />1041&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where libname="WORK" and<BR />1042&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; memname="T_E_M_P"<BR />1043&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1044&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; quit;<BR />1045&nbsp;&nbsp;&nbsp; %end;<BR />1046<BR />1047&nbsp; /*Populate var parameter in the event it has a null value*/<BR />1048&nbsp;&nbsp;&nbsp; %if %length(&amp;var.) eq 0 %then %do;<BR />1049&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; data t_e_m_p;<BR />1050&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set &amp;libname_in..&amp;data. (obs=1 drop=&amp;by. &amp;id. &amp;copy.);<BR />1051&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; run;<BR />1052<BR />1053&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; proc sql noprint;<BR />1054&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select name<BR />1055&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :var separated by " "<BR />1056&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from dictionary.columns<BR />1057&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where libname="WORK" and<BR />1058&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; memname="T_E_M_P"<BR />1059&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;autovars.")) eq "CHAR" %then %do;<BR />1060&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and type="char"<BR />1061&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1062&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else %if %sysfunc(upcase("&amp;autovars.")) ne "ALL" %then %do;<BR />1063&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and type="num"<BR />1064&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1065&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1066&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; quit;<BR />1067&nbsp;&nbsp;&nbsp; %end;<BR />1068<BR />1069&nbsp; /*Initialize macro variables*/<BR />1070&nbsp;&nbsp;&nbsp; %let vars_char=;<BR />1071&nbsp;&nbsp;&nbsp; %let varlist_char=;<BR />1072&nbsp;&nbsp;&nbsp; %let vars_num=;<BR />1073&nbsp;&nbsp;&nbsp; %let varlist_num=;<BR />1074&nbsp;&nbsp;&nbsp; %let formats_char=;<BR />1075&nbsp;&nbsp;&nbsp; %let format_char=;<BR />1076&nbsp;&nbsp;&nbsp; %let formats_num=;<BR />1077&nbsp;&nbsp;&nbsp; %let format_num=;<BR />1078<BR />1079&nbsp; /*Create file t_e_m_p to contain one record with all var variables*/<BR />1080&nbsp;&nbsp;&nbsp; data t_e_m_p;<BR />1081&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set &amp;libname_in..&amp;data. (obs=1 keep=&amp;var.);<BR />1082&nbsp;&nbsp;&nbsp; run;<BR />1083<BR />1084&nbsp; /*Create macro variables containing untransposed var names and formats*/<BR />1085&nbsp;&nbsp;&nbsp; proc sql noprint;<BR />1086&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select name, case<BR />1087&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; when missing(format) then " $"||strip(put(length,5.))||'.'<BR />1088&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else strip(format)<BR />1089&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end<BR />1090&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :vars_char separated by " ",<BR />1091&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :formats_char separated by "~"<BR />1092&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from dictionary.columns<BR />1093&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where libname="WORK" and<BR />1094&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; memname="T_E_M_P" and<BR />1095&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; type="char"<BR />1096&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1097&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select name, case<BR />1098&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; when missing(format) then "best12."<BR />1099&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else strip(format)<BR />1100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end<BR />1101&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :vars_num separated by " ",<BR />1102&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :formats_num separated by "~"<BR />1103&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from dictionary.columns<BR />1104&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where libname="WORK" and<BR />1105&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; memname="T_E_M_P" and<BR />1106&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; type="num"<BR />1107&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1108&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select name<BR />1109&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :vars_all separated by " "<BR />1110&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from dictionary.columns<BR />1111&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where libname="WORK" and<BR />1112&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; memname="T_E_M_P"<BR />1113&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1114&nbsp;&nbsp;&nbsp; quit;<BR />1115<BR />1116&nbsp; /*If sort parameter has a value of YES, create a sorted temporary data file*/<BR />1117&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;sort.")) eq "YES" %then %do;<BR />1118&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let notsorted=;<BR />1119&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; proc sort data=&amp;libname_in..&amp;data.<BR />1120&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (<BR />1121&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; keep=&amp;by. &amp;id. &amp;vars_char. &amp;vars_num. &amp;to_copy.<BR />1122&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;dsoptions.<BR />1123&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )<BR />1124&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; out=t_e_m_p &amp;sort_options. noequals;<BR />1125&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; by &amp;by.;<BR />1126&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; run;<BR />1127&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let data=t_e_m_p;<BR />1128&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let libname_in=work;<BR />1129&nbsp;&nbsp;&nbsp; %end;<BR />1130&nbsp;&nbsp;&nbsp; %else %do;<BR />1131&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let notsorted=notsorted;<BR />1132&nbsp;&nbsp;&nbsp; %end;<BR />1133<BR />1134&nbsp;&nbsp;&nbsp; /*if no id parameter is present, create one from &amp;newid.*/<BR />1135&nbsp;&nbsp;&nbsp; %if %length(&amp;id.) eq 0 %then %do;<BR />1136&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; data t_e_m_p;<BR />1137&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set &amp;libname_in..&amp;data.;<BR />1138&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; by &amp;by.;<BR />1139&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if first.&amp;lastby then &amp;newid.=1;<BR />1140&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else &amp;newid+1;<BR />1141&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; run;<BR />1142&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let id=&amp;newid.;<BR />1143&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let data=t_e_m_p;<BR />1144&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let libname_in=work;<BR />1145&nbsp;&nbsp;&nbsp; %end;<BR />1146<BR />1147&nbsp; /*Ensure guessingrows parameter contains a value*/<BR />1148&nbsp;&nbsp;&nbsp; %if %length(&amp;guessingrows.) eq 0 %then %do;<BR />1149&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let guessingrows=%sysfunc(constant(EXACTINT));<BR />1150&nbsp;&nbsp;&nbsp; %end;<BR />1151<BR />1152&nbsp; /*Ensure a format is assigned to an id variable*/<BR />1153&nbsp;&nbsp;&nbsp; %if %length(&amp;id.) gt 0 %then %do;<BR />1154&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; proc sql noprint;<BR />1155&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select type,length,%sysfunc(strip(format))<BR />1156&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :tr_macro_type, :tr_macro_len, :tr_macro_format<BR />1157&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from dictionary.columns<BR />1158&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where libname="%sysfunc(upcase(&amp;libname_in.))" and<BR />1159&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; memname="%sysfunc(upcase(&amp;data.))" and<BR />1160&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; upcase(name)="%sysfunc(upcase(&amp;id.))"<BR />1161&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1162&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; quit;<BR />1163<BR />1164&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %length(&amp;format.) eq 0 %then %do;<BR />1165&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let optsave=%sysfunc(getoption(missing),$quote.);<BR />1166&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; options missing=.;<BR />1167&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %length(&amp;tr_macro_format.) gt 0 %then %do;<BR />1168&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let format=&amp;tr_macro_format.;<BR />1169&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1170&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else %if "&amp;tr_macro_type." eq "num " %then %do;<BR />1171&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let format=%sysfunc(catt(best,&amp;tr_macro_len.,%str(.)));<BR />1172&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1173&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else %do;<BR />1174&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let format=%sysfunc(catt($,&amp;tr_macro_len.,%str(.)));<BR />1175&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1176&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; options missing=&amp;optsave;<BR />1177&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1178&nbsp;&nbsp;&nbsp; %end;<BR />1179<BR />1180&nbsp; /*Create macro variables containing ordered lists of the requested transposed variable<BR />1181&nbsp;&nbsp;&nbsp; names for character (varlist_char) and numeric (varlist_num) var variables */<BR />1182&nbsp;&nbsp;&nbsp; %if %length(&amp;preloadfmt.) gt 0 %then %do;<BR />1183&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(countw(&amp;preloadfmt.)) eq 1 %then %do;<BR />1184&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let preloadfmt=&amp;libname_in..&amp;preloadfmt.;<BR />1185&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1186&nbsp;&nbsp;&nbsp; %end;<BR />1187&nbsp;&nbsp;&nbsp; %else %do;<BR />1188&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;sort.")) eq "YES" %then<BR />1189&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let dsoptions=;<BR />1190&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; proc freq data=&amp;libname_in..&amp;data. (obs=&amp;guessingrows. keep=&amp;id. &amp;dsoptions.)<BR />1191&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; noprint;<BR />1192&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tables &amp;id./out=_for_format (keep=&amp;id.);<BR />1193&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; run;<BR />1194&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;descendingid.")) eq "YES" %then %do;<BR />1195&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; proc sort data=_for_format;<BR />1196&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; by descending &amp;id;<BR />1197&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; run;<BR />1198&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1199&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; data _for_format;<BR />1200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set _for_format;<BR />1201&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; order=_n_;<BR />1202&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; run;<BR />1203&nbsp;&nbsp;&nbsp; %end;<BR />1204<BR />1205&nbsp;&nbsp; proc sql noprint;<BR />1206&nbsp;&nbsp;&nbsp; %do i=1 %to 2;<BR />1207&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if &amp;i. eq 1 %then %let i_type=char;<BR />1208&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else %let i_type=num;<BR />1209&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %length(&amp;&amp;vars_&amp;i_type.) gt 0 %then %do;<BR />1210&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select distinct<BR />1211&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do j=1 %to 2;<BR />1212&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if &amp;j. eq 1 %then %let j_type=;<BR />1213&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else %let j_type=format;<BR />1214&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do k=1 %to %sysfunc(countw(&amp;&amp;vars_&amp;i_type.));<BR />1215&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "&amp;j_type. "||cats("&amp;prefix.",<BR />1216&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;var_first.")) eq "NO" %then %do;<BR />1217&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; put(&amp;id.,&amp;format),"&amp;delimiter."<BR />1218&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;use_varname.")) ne "NO" %then<BR />1219&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,scan("&amp;&amp;vars_&amp;i_type.",&amp;k.);<BR />1220&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1221&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else %do;<BR />1222&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;use_varname.")) ne "NO" %then<BR />1223&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; scan("&amp;&amp;vars_&amp;i_type.",&amp;k.),;<BR />1224&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "&amp;delimiter.",put(&amp;id.,&amp;format)<BR />1225&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1226&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )<BR />1227&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if &amp;j. eq 2 %then<BR />1228&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ||" "||cats(scan("&amp;&amp;formats_&amp;i_type.",&amp;k.,"~"),";");<BR />1229&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if &amp;k. lt %sysfunc(countw(&amp;&amp;vars_&amp;i_type.)) %then ||;<BR />1230&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else ,;<BR />1231&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1232&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1233&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if "&amp;tr_macro_type." eq "num " %then &amp;id. format=best12.;<BR />1234&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else &amp;id.;<BR />1235&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,order<BR />1236&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :varlist_&amp;i_type. separated by " ",<BR />1237&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :format_&amp;i_type. separated by " ",<BR />1238&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :idlist separated by " ",<BR />1239&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :idorder separated by " "<BR />1240&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %length(&amp;preloadfmt.) gt 0 %then from &amp;preloadfmt.;<BR />1241&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else from _for_format;<BR />1242&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; order by order<BR />1243&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1244&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let num_numlabels=&amp;sqlobs.;<BR />1245&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1246&nbsp;&nbsp;&nbsp; %end;<BR />1247&nbsp;&nbsp;&nbsp; quit;<BR />1248<BR />1249&nbsp;&nbsp;&nbsp; proc sql noprint;<BR />1250&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select distinct<BR />1251&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let j_type=;<BR />1252&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do k=1 %to %sysfunc(countw(&amp;&amp;vars_all.));<BR />1253&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "&amp;j_type. "||cats("&amp;prefix.",<BR />1254<BR />1255&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;var_first.")) eq "NO" %then %do;<BR />1256&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; put(&amp;id.,&amp;format),"&amp;delimiter.",<BR />1257&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;use_varname.")) ne "NO" %then<BR />1258&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; scan("&amp;&amp;vars_all.",&amp;k.);<BR />1259&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )<BR />1260&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1261&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else %do;<BR />1262&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;use_varname.")) ne "NO" %then<BR />1263&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; scan("&amp;&amp;vars_all.",&amp;k.),;<BR />1264&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "&amp;delimiter.",put(&amp;id.,&amp;format))<BR />1265&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1266&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if &amp;k. lt %sysfunc(countw(&amp;&amp;vars_all.)) %then ||;<BR />1267&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else ,;<BR />1268&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1269&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; order<BR />1270&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :varlist_all separated by " ",<BR />1271&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :idorder separated by " "<BR />1272&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %length(&amp;preloadfmt.) gt 0 %then from &amp;preloadfmt.;<BR />1273&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else from _for_format;<BR />1274&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; order by order<BR />1275&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1276&nbsp;&nbsp;&nbsp; quit;<BR />1277<BR />1278&nbsp; /*Create a format that will be used to assign values to the transposed variables*/<BR />1279&nbsp;&nbsp;&nbsp; data _for_format;<BR />1280&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %length(&amp;preloadfmt.) gt 0 %then set &amp;preloadfmt. (rename=(&amp;id.=start));<BR />1281&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else set _for_format&nbsp; (rename=(&amp;id.=start));<BR />1282&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1283&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if "&amp;tr_macro_type." eq "num " %then retain fmtname "labelfmt" type "N";<BR />1284&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else retain fmtname "$labelfmt" type "C";<BR />1285&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1286&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; label=<BR />1287&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %length(&amp;preloadfmt.) eq 0 %then _n_-1;<BR />1288&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else order-1;<BR />1289&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1290&nbsp;&nbsp;&nbsp; run;<BR />1291<BR />1292&nbsp;&nbsp;&nbsp; proc format cntlin = _for_format;<BR />1293&nbsp;&nbsp;&nbsp; run ;<BR />1294<BR />1295&nbsp; /*Create and run the datastep that does the transposition*/<BR />1296&nbsp;&nbsp;&nbsp; data &amp;libname_out..&amp;out.;<BR />1297&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set &amp;libname_in..&amp;data. (keep=&amp;by. &amp;id.<BR />1298&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do i=1 %to %sysfunc(countw("&amp;vars_char."));<BR />1299&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %scan(&amp;vars_char.,&amp;i.)<BR />1300&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1301&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do i=1 %to %sysfunc(countw("&amp;vars_num."));<BR />1302&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %scan(&amp;vars_num.,&amp;i.)<BR />1303&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1304&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do i=1 %to %sysfunc(countw("&amp;to_copy."));<BR />1305&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %scan(&amp;to_copy.,&amp;i.)<BR />1306&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1307&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;dsoptions.<BR />1308&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; );<BR />1309&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; by &amp;by. &amp;notsorted.;<BR />1310&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;format_char. &amp;format_num.<BR />1311&nbsp;&nbsp;&nbsp; %if %length(&amp;vars_char.) gt 0 %then %do;<BR />1312&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; array want_char(*) $<BR />1313&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do i=1 %to %eval(&amp;num_numlabels.*%sysfunc(countw("&amp;vars_char.")));<BR />1314&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %scan(&amp;varlist_char.,&amp;i.)<BR />1315&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1316&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1317&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; array have_char(*) $ &amp;vars_char.;<BR />1318&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; retain want_char;<BR />1319&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if first.&amp;lastby. then call missing(of want_char(*));<BR />1320&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ___nchar=put(&amp;id.,labelfmt.)*dim(have_char);<BR />1321&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; do ___i=1 to dim(have_char);<BR />1322&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; want_char(___nchar+___i)=have_char(___i);<BR />1323&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end;<BR />1324&nbsp;&nbsp;&nbsp; %end;<BR />1325&nbsp;&nbsp;&nbsp; %if %length(&amp;vars_num.) gt 0 %then %do;<BR />1326&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; array want_num(*)<BR />1327&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do i=1 %to %eval(&amp;num_numlabels.*%sysfunc(countw("&amp;vars_num.")));<BR />1328&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %scan(&amp;varlist_num.,&amp;i.)<BR />1329&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1330&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1331&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; array have_num(*) &amp;vars_num.;<BR />1332&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; retain want_num;<BR />1333&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if first.&amp;lastby. then call missing(of want_num(*));<BR />1334&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ___nnum=put(&amp;id.,labelfmt.)*dim(have_num);<BR />1335&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; do ___i=1 to dim(have_num);<BR />1336&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; want_num(___nnum+___i)=have_num(___i);<BR />1337&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end;<BR />1338&nbsp;&nbsp;&nbsp; %end;<BR />1339&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; drop &amp;id. ___: &amp;var. &amp;drop.;<BR />1340&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if last.&amp;lastby. then output;<BR />1341&nbsp;&nbsp;&nbsp; run;<BR />1342<BR />1343&nbsp;&nbsp;&nbsp; data &amp;libname_out..&amp;out.;<BR />1344&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; retain &amp;by. &amp;to_copy. &amp;varlist_all.;<BR />1345&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set &amp;libname_out..&amp;out.;<BR />1346&nbsp;&nbsp;&nbsp; run;<BR />1347<BR />1348&nbsp; /*Delete all temporary files*/<BR />1349&nbsp;&nbsp;&nbsp; proc delete data=work.t_e_m_p work._for_format;<BR />1350&nbsp;&nbsp;&nbsp; run;<BR />1351<BR />1352&nbsp; %mend transpose;<BR />1353&nbsp; options NOQUOTELENMAX;<BR />1354&nbsp; proc sort data=have out=need nodupkey;<BR />1355&nbsp;&nbsp;&nbsp; by time_m sym_root;<BR />1356&nbsp; run;<BR />1357<BR />1358&nbsp; %transpose(data=need, out=want, by=time_m, id=sym_root,<BR />1359&nbsp; delimiter=_,var=price size)</P> Sat, 27 May 2017 02:16:26 GMT kbl 2017-05-27T02:16:26Z https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/359108#M64353 <P>Good afternoon,<BR />I pulled some TAQ data, and the file is in a .CSV format. I've only kept (and am interested in ) about six variables. The data pulled just fine, but it is listed in a column, with all trades for one company listed, followed by all trades for the next company, etc. I need the file sorted so that i can see all companies that traded at a particular time (along with the number of shares traded). In essence, instead of having vertical data, I want horizontal data. Can you help? I am attaching one of the files so you can see what i mean. Thanks very much for any assistance you can provide.<BR /></P> Tue, 16 May 2017 16:47:14 GMT https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/359108#M64353 kbl 2017-05-16T16:47:14Z https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/359114#M64356 <P>Can you attach an example showing how you want the resulting file to look?</P> <P>&nbsp;</P> <P>Art, CEO, AnalystFinder.com</P> Tue, 16 May 2017 17:11:40 GMT https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/359114#M64356 art297 2017-05-16T17:11:40Z https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/359122#M64358 <P>What you're asking for is a report.&nbsp; The data should remain in vertical form.&nbsp; A report might display the data in horizontal form, if that is useful to you.&nbsp; It becomes a question of whether it is worth it to you to learn some programming skills, to be able to produce the report that you want.</P> Tue, 16 May 2017 17:36:07 GMT https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/359122#M64358 Astounding 2017-05-16T17:36:07Z https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/359184#M64366 <TABLE><TBODY><TR><TD>Time</TD><TD>A</TD><TD>AA</TD><TD>AAPL</TD><TD>other stocks…</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;price</TD><TD>price</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;price</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR></TBODY></TABLE><P>Art,</P><P>&nbsp;</P><P>Thanks for your reply.&nbsp; The main shift I want is that I want each company' sym_Root&nbsp; to be a separate column.&nbsp; Time remains the first column.&nbsp; Then column two will be the first company that trades at a given time with the price it traded at for that time.&nbsp; The next column will be the second company and its trading price (if it traded at that time), etc.</P> Tue, 16 May 2017 20:17:18 GMT https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/359184#M64366 kbl 2017-05-16T20:17:18Z https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/359186#M64367 <P>Good afternoon,</P><P>&nbsp;</P><P>I have programmed before, although it's been a couple of years since i used SAS.&nbsp;</P><P>&nbsp;</P><P>I need the tickers (SYM_ROOT) to be in a separate column for each stock with the price at which it is trading for a given time listed under the company ticker.&nbsp; As a result, I don't think it is sufficient to create a report; I think I need to transpose the data.&nbsp; Does that sound right?</P> Tue, 16 May 2017 20:20:17 GMT https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/359186#M64367 kbl 2017-05-16T20:20:17Z https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/359189#M64369 <P>What you want to do is easy with proc transpose, but you have some duplicate entries for some companies at the same times. As long as the duplicates are irrelevant, you can get what you want with:</P> <PRE>proc sort data=have nodupkey; by time_m sym_root; run; proc transpose data=have out=want (drop=_:); by time_m; id sym_root; var price; run; </PRE> <P>Art, CEO, AnalystFinder.com</P> Tue, 16 May 2017 20:27:59 GMT https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/359189#M64369 art297 2017-05-16T20:27:59Z https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/359211#M64371 <P>Thanks so much Art!&nbsp; This is exceptionally helpful.&nbsp; Can I trouble you for one more?&nbsp; I was going to do some other calculations in Excel, but I think it will be easier to use SAS for everything.</P><P>&nbsp;</P><P>What changes do I need to make to the program if I want everything that you just sent, plus I want to keep the volume data by company.&nbsp; So, after I have a column for each company's price data, I have a set of columns for each company's volume data?&nbsp; I think there is not a duplicate issue; each time listed represents the last trade that minute for each company's stock, and we only need to sort to the minute (the first four digits), not to the second (the final digit in the time variable) .&nbsp; The data can look something like this:</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Time (end of each minute)</TD><TD>A</TD><TD>Stock2</TD><TD>Stock 3</TD><TD>Stock 4</TD><TD>Stock 5</TD><TD>…</TD><TD>Stock 500</TD><TD>&nbsp;</TD><TD>A</TD><TD>Stock2</TD><TD>Stock 3</TD><TD>Stock 4</TD><TD>Stock 5</TD><TD>…</TD></TR><TR><TD>30:46</TD><TD>36.5</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>100</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>31:58</TD><TD>36.445</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>The last trade price for each stock each minute here</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>100</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>The corresponding trading volume</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>32:22</TD><TD>36.51</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>100</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;for each stock&nbsp; for each minute here</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>33:55</TD><TD>36.55</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>We&nbsp; have 500 stocks&nbsp; in the index</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>200</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>34:36</TD><TD>36.575</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>and we&nbsp; need end-of-minute price data here for only 5 days</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>100</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>35:57</TD><TD>36.57</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>so it should not be too large of a data file</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>100</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>36:50</TD><TD>36.75</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>100</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>37:58</TD><TD>36.77</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>100</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>38:43</TD><TD>36.76</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>100</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR></TBODY></TABLE> Tue, 16 May 2017 21:35:20 GMT https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/359211#M64371 kbl 2017-05-16T21:35:20Z https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/359214#M64372 <P>You can do it with a couple of proc transposes, but I always find the following macro easier to make a wide file (2 or more variable) wider:&nbsp;<A href="http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset" target="_blank">http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset</A></P> <P>&nbsp;</P> <P>If you download and run that macro, here is all you then have to submit to get what you want:</P> <PRE>proc sort data=have out=need nodupkey; by time_m sym_root; run; %transpose(data=need, out=want, by=time_m, id=sym_root, delimiter=_,var=price size) </PRE> <P>Art, CEO, AnalystFinder.com</P> <P>&nbsp;</P> Tue, 16 May 2017 21:52:00 GMT https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/359214#M64372 art297 2017-05-16T21:52:00Z https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362114#M64512 <P>Art,</P><P>&nbsp;</P><P>Thanks for your help, and my apologies for the delayed response.&nbsp; It took me awhile to get my data to read into the program.&nbsp; That's done now, but I'm not following your comment about running the macro first and then add this code.&nbsp; I went through the paper and the PowerPoint, but can you help with my next step?&nbsp; Here is the program:</P><P>&nbsp;</P><P>Filename three 'G:\threeone.dat';<BR />&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; data have;<BR />&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; infile&nbsp; three expandtabs;<BR />&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; input TIME_M $ SYM_ROOT $ SIZE PRICE TIME2;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;<BR />&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;* out= need;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;<BR />proc sort data=have out=need nodupkey;<BR />&nbsp; by time_m sym_root;<BR />&nbsp; prefix=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; var=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; autovars=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; id=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; descendingid=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; var_first=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; format=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; delimiter=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; copy=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; drop=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sort=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sort_options=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; use_varname=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; preloadfmt=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; guessingrows=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; newid=);<BR />run;<BR /><BR />%transpose(data=need, out=want, by=time_m, id=sym_root,<BR />delimiter=_,var=price size)<BR />run;<BR /><BR /></P><P>Thanks a lot.</P> Sat, 27 May 2017 01:31:09 GMT https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362114#M64512 kbl 2017-05-27T01:31:09Z https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362115#M64513 <P>There was a link to the code on that page. The direct link to the code is:&nbsp;<A href="http://www.sascommunity.org/mwiki/images/b/be/BB-07-2013.sas" target="_blank">http://www.sascommunity.org/mwiki/images/b/be/BB-07-2013.sas</A></P> <P>&nbsp;</P> <P>Copy and paste that code into whatever you use to submit SAS code. After the last line of the macro's code, paste the following lines :</P> <PRE>Filename three 'G:\threeone.dat';<BR />data have;<BR /> &nbsp;infile&nbsp; three expandtabs;<BR /> &nbsp;input TIME_M $ SYM_ROOT $ SIZE PRICE TIME2;<BR />run;<BR /> <BR />proc sort data=have out=need nodupkey; by time_m sym_root; run; %transpose(data=need, out=want, by=time_m, id=sym_root, delimiter=_,var=price size)</PRE> <P>Then click the run icon.</P> <P>&nbsp;</P> <P>Art, CEO, AnalystFinder.com</P> <P>&nbsp;</P> <P>&nbsp;</P> Sat, 27 May 2017 01:48:05 GMT https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362115#M64513 art297 2017-05-27T01:48:05Z https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362117#M64514 <P>Art,</P><P>&nbsp;</P><P>Is all of this code included?&nbsp; I ran this program, and did not get any errors, but I also did not get any output:</P><P>&nbsp;</P><P>%macro transpose(libname_in=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; libname_out=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; data=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; out=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; by=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; prefix=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; var=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; autovars=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; id=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; descendingid=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; var_first=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; format=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; delimiter=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; copy=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; drop=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sort=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sort_options=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; use_varname=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; preloadfmt=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; guessingrows=,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; newid=);<BR /><BR />/*Check whether the data and out parameters contain one or two-level filenames*/<BR />&nbsp; %let lp=%sysfunc(findc(%superq(data),%str(%()));<BR />&nbsp; %if &amp;lp. %then %do;<BR />&nbsp;&nbsp;&nbsp; %let rp=%sysfunc(findc(%superq(data),%str(%)),b));<BR />&nbsp;&nbsp;&nbsp; %let dsoptions=%qsysfunc(substrn(%nrstr(%superq(data)),&amp;lp+1,&amp;rp-&amp;lp-1));<BR />&nbsp;&nbsp;&nbsp; %let data=%sysfunc(substrn(%nrstr(%superq(data)),1,%eval(&amp;lp-1)));<BR />&nbsp; %end;<BR />&nbsp; %else %let dsoptions=;<BR />&nbsp; %if %sysfunc(countw(&amp;data.)) eq 2 %then %do;<BR />&nbsp;&nbsp;&nbsp; %let libname_in=%scan(&amp;data.,1);<BR />&nbsp;&nbsp;&nbsp; %let data=%scan(&amp;data.,2);<BR />&nbsp; %end;<BR />&nbsp; %else %if %length(&amp;libname_in.) eq 0 %then %do;<BR />&nbsp;&nbsp;&nbsp; %let libname_in=work;<BR />&nbsp; %end;<BR /><BR />&nbsp; %if %sysfunc(countw(&amp;out.)) eq 2 %then %do;<BR />&nbsp;&nbsp;&nbsp; %let libname_out=%scan(&amp;out.,1);<BR />&nbsp;&nbsp;&nbsp; %let out=%scan(&amp;out.,2);<BR />&nbsp; %end;<BR />&nbsp; %else %if %length(&amp;libname_out.) eq 0 %then %do;<BR />&nbsp;&nbsp;&nbsp; %let libname_out=work;<BR />&nbsp; %end;<BR /><BR />&nbsp; %if %length(&amp;newid.) eq 0 %then %do;<BR />&nbsp;&nbsp;&nbsp; %let newid=row;<BR />&nbsp; %end;<BR /><BR />&nbsp; /*obtain last by variable*/<BR />&nbsp; %if %length(&amp;by.) gt 0 %then %do;<BR />&nbsp;&nbsp;&nbsp; %let lastby=%scan(&amp;by.,-1);<BR />&nbsp; %end;<BR />&nbsp; %else %do;<BR />&nbsp;&nbsp;&nbsp; %let lastby=;<BR />&nbsp; %end;<BR /><BR />/*Create macro variable to contain a list of variables to be copied*/<BR />&nbsp;%let to_copy=;<BR />&nbsp; %if %length(&amp;copy.) gt 0 %then %do;<BR />&nbsp;&nbsp;&nbsp; data t_e_m_p;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set &amp;libname_in..&amp;data. (obs=1 keep=&amp;copy.);<BR />&nbsp;&nbsp;&nbsp; run;<BR /><BR />&nbsp;&nbsp;&nbsp; proc sql noprint;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select name<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :to_copy separated by " "<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from dictionary.columns<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where libname="WORK" and<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; memname="T_E_M_P"<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; quit;<BR />&nbsp; %end;<BR /><BR />/*Populate var parameter in the event it has a null value*/<BR />&nbsp; %if %length(&amp;var.) eq 0 %then %do;<BR />&nbsp;&nbsp;&nbsp; data t_e_m_p;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set &amp;libname_in..&amp;data. (obs=1 drop=&amp;by. &amp;id. &amp;copy.);<BR />&nbsp;&nbsp;&nbsp; run;<BR /><BR />&nbsp;&nbsp;&nbsp; proc sql noprint;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select name<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :var separated by " "<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from dictionary.columns<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where libname="WORK" and<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; memname="T_E_M_P"<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;autovars.")) eq "CHAR" %then %do;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and type="char"<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else %if %sysfunc(upcase("&amp;autovars.")) ne "ALL" %then %do;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and type="num"<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; quit;<BR />&nbsp; %end;<BR />&nbsp;<BR />/*Initialize macro variables*/<BR />&nbsp; %let vars_char=;<BR />&nbsp; %let varlist_char=;<BR />&nbsp; %let vars_num=;<BR />&nbsp; %let varlist_num=;<BR />&nbsp; %let formats_char=;<BR />&nbsp; %let format_char=;<BR />&nbsp; %let formats_num=;<BR />&nbsp; %let format_num=;<BR /><BR />/*Create file t_e_m_p to contain one record with all var variables*/<BR />&nbsp; data t_e_m_p;<BR />&nbsp;&nbsp;&nbsp; set &amp;libname_in..&amp;data. (obs=1 keep=&amp;var.);<BR />&nbsp; run;<BR /><BR />/*Create macro variables containing untransposed var names and formats*/<BR />&nbsp; proc sql noprint;<BR />&nbsp;&nbsp;&nbsp; select name, case<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; when missing(format) then " $"||strip(put(length,5.))||'.'<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else strip(format)<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :vars_char separated by " ",<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :formats_char separated by "~"<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from dictionary.columns<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where libname="WORK" and<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; memname="T_E_M_P" and<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; type="char"<BR />&nbsp;&nbsp;&nbsp; ;<BR />&nbsp;&nbsp;&nbsp; select name, case<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; when missing(format) then "best12."<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else strip(format)<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :vars_num separated by " ",<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :formats_num separated by "~"<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from dictionary.columns<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where libname="WORK" and<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; memname="T_E_M_P" and<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; type="num"<BR />&nbsp;&nbsp;&nbsp; ;<BR />&nbsp;&nbsp;&nbsp; select name<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :vars_all separated by " "<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from dictionary.columns<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where libname="WORK" and<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; memname="T_E_M_P"<BR />&nbsp;&nbsp;&nbsp; ;<BR />&nbsp; quit;<BR /><BR />/*If sort parameter has a value of YES, create a sorted temporary data file*/<BR />&nbsp; %if %sysfunc(upcase("&amp;sort.")) eq "YES" %then %do;<BR />&nbsp;&nbsp;&nbsp; %let notsorted=;<BR />&nbsp;&nbsp;&nbsp; proc sort data=&amp;libname_in..&amp;data.<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; keep=&amp;by. &amp;id. &amp;vars_char. &amp;vars_num. &amp;to_copy.<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;dsoptions.<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; out=t_e_m_p &amp;sort_options. noequals;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; by &amp;by.;<BR />&nbsp;&nbsp;&nbsp; run;<BR />&nbsp;&nbsp;&nbsp; %let data=t_e_m_p;<BR />&nbsp;&nbsp;&nbsp; %let libname_in=work;<BR />&nbsp; %end;<BR />&nbsp; %else %do;<BR />&nbsp;&nbsp;&nbsp; %let notsorted=notsorted;<BR />&nbsp; %end;<BR /><BR />&nbsp; /*if no id parameter is present, create one from &amp;newid.*/<BR />&nbsp; %if %length(&amp;id.) eq 0 %then %do;<BR />&nbsp;&nbsp;&nbsp; data t_e_m_p;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set &amp;libname_in..&amp;data.;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; by &amp;by.;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if first.&amp;lastby then &amp;newid.=1;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else &amp;newid+1;<BR />&nbsp;&nbsp;&nbsp; run;<BR />&nbsp;&nbsp;&nbsp; %let id=&amp;newid.;<BR />&nbsp;&nbsp;&nbsp; %let data=t_e_m_p;<BR />&nbsp;&nbsp;&nbsp; %let libname_in=work;<BR />&nbsp; %end;<BR /><BR />/*Ensure guessingrows parameter contains a value*/<BR />&nbsp; %if %length(&amp;guessingrows.) eq 0 %then %do;<BR />&nbsp;&nbsp;&nbsp; %let guessingrows=%sysfunc(constant(EXACTINT));<BR />&nbsp; %end;<BR /><BR />/*Ensure a format is assigned to an id variable*/<BR />&nbsp; %if %length(&amp;id.) gt 0 %then %do;<BR />&nbsp;&nbsp;&nbsp; proc sql noprint;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select type,length,%sysfunc(strip(format))<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :tr_macro_type, :tr_macro_len, :tr_macro_format<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from dictionary.columns<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where libname="%sysfunc(upcase(&amp;libname_in.))" and<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; memname="%sysfunc(upcase(&amp;data.))" and<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; upcase(name)="%sysfunc(upcase(&amp;id.))"<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />&nbsp;&nbsp;&nbsp; quit;<BR /><BR />&nbsp;&nbsp;&nbsp; %if %length(&amp;format.) eq 0 %then %do;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let optsave=%sysfunc(getoption(missing),$quote.);<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; options missing=.;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %length(&amp;tr_macro_format.) gt 0 %then %do;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let format=&amp;tr_macro_format.;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else %if "&amp;tr_macro_type." eq "num " %then %do;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let format=%sysfunc(catt(best,&amp;tr_macro_len.,%str(.)));<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else %do;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let format=%sysfunc(catt($,&amp;tr_macro_len.,%str(.)));<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; options missing=&amp;optsave;<BR />&nbsp;&nbsp;&nbsp; %end;<BR />&nbsp; %end;<BR /><BR />/*Create macro variables containing ordered lists of the requested transposed variable<BR />&nbsp; names for character (varlist_char) and numeric (varlist_num) var variables */<BR />&nbsp; %if %length(&amp;preloadfmt.) gt 0 %then %do;<BR />&nbsp;&nbsp;&nbsp; %if %sysfunc(countw(&amp;preloadfmt.)) eq 1 %then %do;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let preloadfmt=&amp;libname_in..&amp;preloadfmt.;<BR />&nbsp;&nbsp;&nbsp; %end;<BR />&nbsp; %end;<BR />&nbsp; %else %do;<BR />&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;sort.")) eq "YES" %then<BR />&nbsp;&nbsp;&nbsp;&nbsp; %let dsoptions=;<BR />&nbsp;&nbsp;&nbsp; proc freq data=&amp;libname_in..&amp;data. (obs=&amp;guessingrows. keep=&amp;id. &amp;dsoptions.)<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; noprint;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tables &amp;id./out=_for_format (keep=&amp;id.);<BR />&nbsp;&nbsp;&nbsp; run;<BR />&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;descendingid.")) eq "YES" %then %do;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; proc sort data=_for_format;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; by descending &amp;id;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; run;<BR />&nbsp;&nbsp;&nbsp; %end;<BR />&nbsp;&nbsp;&nbsp; data _for_format;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set _for_format;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; order=_n_;<BR />&nbsp;&nbsp;&nbsp; run;<BR />&nbsp; %end;<BR /><BR />&nbsp;proc sql noprint;<BR />&nbsp; %do i=1 %to 2;<BR />&nbsp;&nbsp;&nbsp; %if &amp;i. eq 1 %then %let i_type=char;<BR />&nbsp;&nbsp;&nbsp; %else %let i_type=num;<BR />&nbsp;&nbsp;&nbsp; %if %length(&amp;&amp;vars_&amp;i_type.) gt 0 %then %do;<BR />&nbsp;&nbsp;&nbsp; select distinct<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do j=1 %to 2;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if &amp;j. eq 1 %then %let j_type=;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else %let j_type=format;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do k=1 %to %sysfunc(countw(&amp;&amp;vars_&amp;i_type.));<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "&amp;j_type. "||cats("&amp;prefix.",<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;var_first.")) eq "NO" %then %do;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; put(&amp;id.,&amp;format),"&amp;delimiter."<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;use_varname.")) ne "NO" %then<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,scan("&amp;&amp;vars_&amp;i_type.",&amp;k.);<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else %do;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;use_varname.")) ne "NO" %then<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; scan("&amp;&amp;vars_&amp;i_type.",&amp;k.),;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "&amp;delimiter.",put(&amp;id.,&amp;format)<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if &amp;j. eq 2 %then<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ||" "||cats(scan("&amp;&amp;formats_&amp;i_type.",&amp;k.,"~"),";");<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if &amp;k. lt %sysfunc(countw(&amp;&amp;vars_&amp;i_type.)) %then ||;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else ,;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if "&amp;tr_macro_type." eq "num " %then &amp;id. format=best12.;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else &amp;id.;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,order<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :varlist_&amp;i_type. separated by " ",<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :format_&amp;i_type. separated by " ",<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :idlist separated by " ",<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :idorder separated by " "<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %length(&amp;preloadfmt.) gt 0 %then from &amp;preloadfmt.;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else from _for_format;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; order by order<BR />&nbsp;&nbsp;&nbsp; ;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let num_numlabels=&amp;sqlobs.;<BR />&nbsp;&nbsp;&nbsp; %end;<BR />&nbsp; %end;<BR />&nbsp; quit;<BR /><BR />&nbsp; proc sql noprint;<BR />&nbsp;&nbsp;&nbsp; select distinct<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let j_type=;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do k=1 %to %sysfunc(countw(&amp;&amp;vars_all.));<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "&amp;j_type. "||cats("&amp;prefix.",<BR />&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;var_first.")) eq "NO" %then %do;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; put(&amp;id.,&amp;format),"&amp;delimiter.",<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;use_varname.")) ne "NO" %then<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; scan("&amp;&amp;vars_all.",&amp;k.);<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else %do;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;use_varname.")) ne "NO" %then<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; scan("&amp;&amp;vars_all.",&amp;k.),;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "&amp;delimiter.",put(&amp;id.,&amp;format))<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if &amp;k. lt %sysfunc(countw(&amp;&amp;vars_all.)) %then ||;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else ,;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; order<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :varlist_all separated by " ",<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :idorder separated by " "<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %length(&amp;preloadfmt.) gt 0 %then from &amp;preloadfmt.;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else from _for_format;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; order by order<BR />&nbsp;&nbsp;&nbsp; ;<BR />&nbsp; quit;<BR /><BR />/*Create a format that will be used to assign values to the transposed variables*/<BR />&nbsp; data _for_format;<BR />&nbsp;&nbsp;&nbsp; %if %length(&amp;preloadfmt.) gt 0 %then set &amp;preloadfmt. (rename=(&amp;id.=start));<BR />&nbsp;&nbsp;&nbsp; %else set _for_format&nbsp; (rename=(&amp;id.=start));<BR />&nbsp;&nbsp;&nbsp; ;<BR />&nbsp;&nbsp;&nbsp; %if "&amp;tr_macro_type." eq "num " %then retain fmtname "labelfmt" type "N";<BR />&nbsp;&nbsp;&nbsp; %else retain fmtname "$labelfmt" type "C";<BR />&nbsp;&nbsp;&nbsp; ;<BR />&nbsp;&nbsp;&nbsp; label=<BR />&nbsp;&nbsp;&nbsp;&nbsp; %if %length(&amp;preloadfmt.) eq 0 %then _n_-1;<BR />&nbsp;&nbsp;&nbsp;&nbsp; %else order-1;<BR />&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />&nbsp; run;<BR /><BR />&nbsp; proc format cntlin = _for_format;<BR />&nbsp; run ;<BR /><BR />/*Create and run the datastep that does the transposition*/<BR />&nbsp; data &amp;libname_out..&amp;out.;<BR />&nbsp;&nbsp;&nbsp; set &amp;libname_in..&amp;data. (keep=&amp;by. &amp;id.<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do i=1 %to %sysfunc(countw("&amp;vars_char."));<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %scan(&amp;vars_char.,&amp;i.)<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do i=1 %to %sysfunc(countw("&amp;vars_num."));<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %scan(&amp;vars_num.,&amp;i.)<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do i=1 %to %sysfunc(countw("&amp;to_copy."));<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %scan(&amp;to_copy.,&amp;i.)<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;dsoptions.<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; );<BR />&nbsp;&nbsp;&nbsp; by &amp;by. &amp;notsorted.;<BR />&nbsp;&nbsp;&nbsp; &amp;format_char. &amp;format_num.<BR />&nbsp; %if %length(&amp;vars_char.) gt 0 %then %do;<BR />&nbsp;&nbsp;&nbsp; array want_char(*) $<BR />&nbsp;&nbsp;&nbsp; %do i=1 %to %eval(&amp;num_numlabels.*%sysfunc(countw("&amp;vars_char.")));<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %scan(&amp;varlist_char.,&amp;i.)<BR />&nbsp;&nbsp;&nbsp; %end;<BR />&nbsp;&nbsp;&nbsp; ;<BR />&nbsp;&nbsp;&nbsp; array have_char(*) $ &amp;vars_char.;<BR />&nbsp;&nbsp;&nbsp; retain want_char;<BR />&nbsp;&nbsp;&nbsp; if first.&amp;lastby. then call missing(of want_char(*));<BR />&nbsp;&nbsp;&nbsp; ___nchar=put(&amp;id.,labelfmt.)*dim(have_char);<BR />&nbsp;&nbsp;&nbsp; do ___i=1 to dim(have_char);<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; want_char(___nchar+___i)=have_char(___i);<BR />&nbsp;&nbsp;&nbsp; end;<BR />&nbsp; %end;<BR />&nbsp; %if %length(&amp;vars_num.) gt 0 %then %do;<BR />&nbsp;&nbsp;&nbsp; array want_num(*)<BR />&nbsp;&nbsp;&nbsp; %do i=1 %to %eval(&amp;num_numlabels.*%sysfunc(countw("&amp;vars_num.")));<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %scan(&amp;varlist_num.,&amp;i.)<BR />&nbsp;&nbsp;&nbsp; %end;<BR />&nbsp;&nbsp;&nbsp; ;<BR />&nbsp;&nbsp;&nbsp; array have_num(*) &amp;vars_num.;<BR />&nbsp;&nbsp;&nbsp; retain want_num;<BR />&nbsp;&nbsp;&nbsp; if first.&amp;lastby. then call missing(of want_num(*));<BR />&nbsp;&nbsp;&nbsp; ___nnum=put(&amp;id.,labelfmt.)*dim(have_num);<BR />&nbsp;&nbsp;&nbsp; do ___i=1 to dim(have_num);<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; want_num(___nnum+___i)=have_num(___i);<BR />&nbsp;&nbsp;&nbsp; end;<BR />&nbsp; %end;<BR />&nbsp;&nbsp;&nbsp; drop &amp;id. ___: &amp;var. &amp;drop.;<BR />&nbsp;&nbsp;&nbsp; if last.&amp;lastby. then output;<BR />&nbsp; run;<BR /><BR />&nbsp; data &amp;libname_out..&amp;out.;<BR />&nbsp;&nbsp;&nbsp; retain &amp;by. &amp;to_copy. &amp;varlist_all.;<BR />&nbsp;&nbsp;&nbsp; set &amp;libname_out..&amp;out.;<BR />&nbsp; run;<BR /><BR />/*Delete all temporary files*/<BR />&nbsp; proc delete data=work.t_e_m_p work._for_format;<BR />&nbsp; run;<BR /><BR />%mend transpose;<BR />options NOQUOTELENMAX;<BR />proc sort data=have out=need nodupkey;<BR />&nbsp; by time_m sym_root;<BR />run;<BR /><BR />%transpose(data=need, out=want, by=time_m, id=sym_root,<BR />delimiter=_,var=price size)</P> Sat, 27 May 2017 02:09:02 GMT https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362117#M64514 kbl 2017-05-27T02:09:02Z https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362119#M64515 <P>Post your log. If the file HAVE was in your work directory, the file WANT should have been created in your work directory.</P> <P>&nbsp;</P> <P>Art, CEO, AnalystFinder.com</P> Sat, 27 May 2017 02:13:09 GMT https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362119#M64515 art297 2017-05-27T02:13:09Z https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362120#M64516 <P>Thanks a lot Art; this is the log:</P><P>&nbsp;</P><P>972&nbsp; %macro transpose(libname_in=,<BR />973&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; libname_out=,<BR />974&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; data=,<BR />975&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; out=,<BR />976&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; by=,<BR />977&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; prefix=,<BR />978&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; var=,<BR />979&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; autovars=,<BR />980&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; id=,<BR />981&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; descendingid=,<BR />982&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; var_first=,<BR />983&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; format=,<BR />984&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; delimiter=,<BR />985&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; copy=,<BR />986&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; drop=,<BR />987&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sort=,<BR />988&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sort_options=,<BR />989&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; use_varname=,<BR />990&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; preloadfmt=,<BR />991&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; guessingrows=,<BR />992&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; newid=);<BR />993<BR />994&nbsp; /*Check whether the data and out parameters contain one or two-level filenames*/<BR />995&nbsp;&nbsp;&nbsp; %let lp=%sysfunc(findc(%superq(data),%str(%()));<BR />996&nbsp;&nbsp;&nbsp; %if &amp;lp. %then %do;<BR />997&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let rp=%sysfunc(findc(%superq(data),%str(%)),b));<BR />998&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let dsoptions=%qsysfunc(substrn(%nrstr(%superq(data)),&amp;lp+1,&amp;rp-&amp;lp-1));<BR />999&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let data=%sysfunc(substrn(%nrstr(%superq(data)),1,%eval(&amp;lp-1)));<BR />1000&nbsp;&nbsp;&nbsp; %end;<BR />1001&nbsp;&nbsp;&nbsp; %else %let dsoptions=;<BR />1002&nbsp;&nbsp;&nbsp; %if %sysfunc(countw(&amp;data.)) eq 2 %then %do;<BR />1003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let libname_in=%scan(&amp;data.,1);<BR />1004&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let data=%scan(&amp;data.,2);<BR />1005&nbsp;&nbsp;&nbsp; %end;<BR />1006&nbsp;&nbsp;&nbsp; %else %if %length(&amp;libname_in.) eq 0 %then %do;<BR />1007&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let libname_in=work;<BR />1008&nbsp;&nbsp;&nbsp; %end;<BR />1009<BR />1010&nbsp;&nbsp;&nbsp; %if %sysfunc(countw(&amp;out.)) eq 2 %then %do;<BR />1011&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let libname_out=%scan(&amp;out.,1);<BR />1012&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let out=%scan(&amp;out.,2);<BR />1013&nbsp;&nbsp;&nbsp; %end;<BR />1014&nbsp;&nbsp;&nbsp; %else %if %length(&amp;libname_out.) eq 0 %then %do;<BR />1015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let libname_out=work;<BR />1016&nbsp;&nbsp;&nbsp; %end;<BR />1017<BR />1018&nbsp;&nbsp;&nbsp; %if %length(&amp;newid.) eq 0 %then %do;<BR />1019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let newid=row;<BR />1020&nbsp;&nbsp;&nbsp; %end;<BR />1021<BR />1022&nbsp;&nbsp;&nbsp; /*obtain last by variable*/<BR />1023&nbsp;&nbsp;&nbsp; %if %length(&amp;by.) gt 0 %then %do;<BR />1024&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let lastby=%scan(&amp;by.,-1);<BR />1025&nbsp;&nbsp;&nbsp; %end;<BR />1026&nbsp;&nbsp;&nbsp; %else %do;<BR />1027&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let lastby=;<BR />1028&nbsp;&nbsp;&nbsp; %end;<BR />1029<BR />1030&nbsp; /*Create macro variable to contain a list of variables to be copied*/<BR />1031&nbsp;&nbsp; %let to_copy=;<BR />1032&nbsp;&nbsp;&nbsp; %if %length(&amp;copy.) gt 0 %then %do;<BR />1033&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; data t_e_m_p;<BR />1034&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set &amp;libname_in..&amp;data. (obs=1 keep=&amp;copy.);<BR />1035&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; run;<BR />1036<BR />1037&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; proc sql noprint;<BR />1038&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select name<BR />1039&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :to_copy separated by " "<BR />1040&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from dictionary.columns<BR />1041&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where libname="WORK" and<BR />1042&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; memname="T_E_M_P"<BR />1043&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1044&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; quit;<BR />1045&nbsp;&nbsp;&nbsp; %end;<BR />1046<BR />1047&nbsp; /*Populate var parameter in the event it has a null value*/<BR />1048&nbsp;&nbsp;&nbsp; %if %length(&amp;var.) eq 0 %then %do;<BR />1049&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; data t_e_m_p;<BR />1050&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set &amp;libname_in..&amp;data. (obs=1 drop=&amp;by. &amp;id. &amp;copy.);<BR />1051&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; run;<BR />1052<BR />1053&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; proc sql noprint;<BR />1054&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select name<BR />1055&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :var separated by " "<BR />1056&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from dictionary.columns<BR />1057&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where libname="WORK" and<BR />1058&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; memname="T_E_M_P"<BR />1059&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;autovars.")) eq "CHAR" %then %do;<BR />1060&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and type="char"<BR />1061&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1062&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else %if %sysfunc(upcase("&amp;autovars.")) ne "ALL" %then %do;<BR />1063&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and type="num"<BR />1064&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1065&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1066&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; quit;<BR />1067&nbsp;&nbsp;&nbsp; %end;<BR />1068<BR />1069&nbsp; /*Initialize macro variables*/<BR />1070&nbsp;&nbsp;&nbsp; %let vars_char=;<BR />1071&nbsp;&nbsp;&nbsp; %let varlist_char=;<BR />1072&nbsp;&nbsp;&nbsp; %let vars_num=;<BR />1073&nbsp;&nbsp;&nbsp; %let varlist_num=;<BR />1074&nbsp;&nbsp;&nbsp; %let formats_char=;<BR />1075&nbsp;&nbsp;&nbsp; %let format_char=;<BR />1076&nbsp;&nbsp;&nbsp; %let formats_num=;<BR />1077&nbsp;&nbsp;&nbsp; %let format_num=;<BR />1078<BR />1079&nbsp; /*Create file t_e_m_p to contain one record with all var variables*/<BR />1080&nbsp;&nbsp;&nbsp; data t_e_m_p;<BR />1081&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set &amp;libname_in..&amp;data. (obs=1 keep=&amp;var.);<BR />1082&nbsp;&nbsp;&nbsp; run;<BR />1083<BR />1084&nbsp; /*Create macro variables containing untransposed var names and formats*/<BR />1085&nbsp;&nbsp;&nbsp; proc sql noprint;<BR />1086&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select name, case<BR />1087&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; when missing(format) then " $"||strip(put(length,5.))||'.'<BR />1088&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else strip(format)<BR />1089&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end<BR />1090&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :vars_char separated by " ",<BR />1091&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :formats_char separated by "~"<BR />1092&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from dictionary.columns<BR />1093&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where libname="WORK" and<BR />1094&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; memname="T_E_M_P" and<BR />1095&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; type="char"<BR />1096&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1097&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select name, case<BR />1098&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; when missing(format) then "best12."<BR />1099&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else strip(format)<BR />1100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end<BR />1101&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :vars_num separated by " ",<BR />1102&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :formats_num separated by "~"<BR />1103&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from dictionary.columns<BR />1104&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where libname="WORK" and<BR />1105&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; memname="T_E_M_P" and<BR />1106&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; type="num"<BR />1107&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1108&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select name<BR />1109&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :vars_all separated by " "<BR />1110&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from dictionary.columns<BR />1111&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where libname="WORK" and<BR />1112&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; memname="T_E_M_P"<BR />1113&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1114&nbsp;&nbsp;&nbsp; quit;<BR />1115<BR />1116&nbsp; /*If sort parameter has a value of YES, create a sorted temporary data file*/<BR />1117&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;sort.")) eq "YES" %then %do;<BR />1118&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let notsorted=;<BR />1119&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; proc sort data=&amp;libname_in..&amp;data.<BR />1120&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (<BR />1121&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; keep=&amp;by. &amp;id. &amp;vars_char. &amp;vars_num. &amp;to_copy.<BR />1122&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;dsoptions.<BR />1123&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )<BR />1124&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; out=t_e_m_p &amp;sort_options. noequals;<BR />1125&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; by &amp;by.;<BR />1126&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; run;<BR />1127&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let data=t_e_m_p;<BR />1128&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let libname_in=work;<BR />1129&nbsp;&nbsp;&nbsp; %end;<BR />1130&nbsp;&nbsp;&nbsp; %else %do;<BR />1131&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let notsorted=notsorted;<BR />1132&nbsp;&nbsp;&nbsp; %end;<BR />1133<BR />1134&nbsp;&nbsp;&nbsp; /*if no id parameter is present, create one from &amp;newid.*/<BR />1135&nbsp;&nbsp;&nbsp; %if %length(&amp;id.) eq 0 %then %do;<BR />1136&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; data t_e_m_p;<BR />1137&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set &amp;libname_in..&amp;data.;<BR />1138&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; by &amp;by.;<BR />1139&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if first.&amp;lastby then &amp;newid.=1;<BR />1140&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else &amp;newid+1;<BR />1141&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; run;<BR />1142&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let id=&amp;newid.;<BR />1143&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let data=t_e_m_p;<BR />1144&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let libname_in=work;<BR />1145&nbsp;&nbsp;&nbsp; %end;<BR />1146<BR />1147&nbsp; /*Ensure guessingrows parameter contains a value*/<BR />1148&nbsp;&nbsp;&nbsp; %if %length(&amp;guessingrows.) eq 0 %then %do;<BR />1149&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let guessingrows=%sysfunc(constant(EXACTINT));<BR />1150&nbsp;&nbsp;&nbsp; %end;<BR />1151<BR />1152&nbsp; /*Ensure a format is assigned to an id variable*/<BR />1153&nbsp;&nbsp;&nbsp; %if %length(&amp;id.) gt 0 %then %do;<BR />1154&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; proc sql noprint;<BR />1155&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select type,length,%sysfunc(strip(format))<BR />1156&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :tr_macro_type, :tr_macro_len, :tr_macro_format<BR />1157&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from dictionary.columns<BR />1158&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where libname="%sysfunc(upcase(&amp;libname_in.))" and<BR />1159&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; memname="%sysfunc(upcase(&amp;data.))" and<BR />1160&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; upcase(name)="%sysfunc(upcase(&amp;id.))"<BR />1161&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1162&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; quit;<BR />1163<BR />1164&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %length(&amp;format.) eq 0 %then %do;<BR />1165&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let optsave=%sysfunc(getoption(missing),$quote.);<BR />1166&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; options missing=.;<BR />1167&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %length(&amp;tr_macro_format.) gt 0 %then %do;<BR />1168&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let format=&amp;tr_macro_format.;<BR />1169&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1170&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else %if "&amp;tr_macro_type." eq "num " %then %do;<BR />1171&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let format=%sysfunc(catt(best,&amp;tr_macro_len.,%str(.)));<BR />1172&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1173&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else %do;<BR />1174&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let format=%sysfunc(catt($,&amp;tr_macro_len.,%str(.)));<BR />1175&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1176&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; options missing=&amp;optsave;<BR />1177&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1178&nbsp;&nbsp;&nbsp; %end;<BR />1179<BR />1180&nbsp; /*Create macro variables containing ordered lists of the requested transposed variable<BR />1181&nbsp;&nbsp;&nbsp; names for character (varlist_char) and numeric (varlist_num) var variables */<BR />1182&nbsp;&nbsp;&nbsp; %if %length(&amp;preloadfmt.) gt 0 %then %do;<BR />1183&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(countw(&amp;preloadfmt.)) eq 1 %then %do;<BR />1184&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let preloadfmt=&amp;libname_in..&amp;preloadfmt.;<BR />1185&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1186&nbsp;&nbsp;&nbsp; %end;<BR />1187&nbsp;&nbsp;&nbsp; %else %do;<BR />1188&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;sort.")) eq "YES" %then<BR />1189&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let dsoptions=;<BR />1190&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; proc freq data=&amp;libname_in..&amp;data. (obs=&amp;guessingrows. keep=&amp;id. &amp;dsoptions.)<BR />1191&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; noprint;<BR />1192&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tables &amp;id./out=_for_format (keep=&amp;id.);<BR />1193&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; run;<BR />1194&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;descendingid.")) eq "YES" %then %do;<BR />1195&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; proc sort data=_for_format;<BR />1196&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; by descending &amp;id;<BR />1197&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; run;<BR />1198&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1199&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; data _for_format;<BR />1200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set _for_format;<BR />1201&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; order=_n_;<BR />1202&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; run;<BR />1203&nbsp;&nbsp;&nbsp; %end;<BR />1204<BR />1205&nbsp;&nbsp; proc sql noprint;<BR />1206&nbsp;&nbsp;&nbsp; %do i=1 %to 2;<BR />1207&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if &amp;i. eq 1 %then %let i_type=char;<BR />1208&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else %let i_type=num;<BR />1209&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %length(&amp;&amp;vars_&amp;i_type.) gt 0 %then %do;<BR />1210&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select distinct<BR />1211&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do j=1 %to 2;<BR />1212&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if &amp;j. eq 1 %then %let j_type=;<BR />1213&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else %let j_type=format;<BR />1214&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do k=1 %to %sysfunc(countw(&amp;&amp;vars_&amp;i_type.));<BR />1215&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "&amp;j_type. "||cats("&amp;prefix.",<BR />1216&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;var_first.")) eq "NO" %then %do;<BR />1217&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; put(&amp;id.,&amp;format),"&amp;delimiter."<BR />1218&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;use_varname.")) ne "NO" %then<BR />1219&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,scan("&amp;&amp;vars_&amp;i_type.",&amp;k.);<BR />1220&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1221&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else %do;<BR />1222&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;use_varname.")) ne "NO" %then<BR />1223&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; scan("&amp;&amp;vars_&amp;i_type.",&amp;k.),;<BR />1224&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "&amp;delimiter.",put(&amp;id.,&amp;format)<BR />1225&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1226&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )<BR />1227&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if &amp;j. eq 2 %then<BR />1228&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ||" "||cats(scan("&amp;&amp;formats_&amp;i_type.",&amp;k.,"~"),";");<BR />1229&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if &amp;k. lt %sysfunc(countw(&amp;&amp;vars_&amp;i_type.)) %then ||;<BR />1230&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else ,;<BR />1231&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1232&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1233&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if "&amp;tr_macro_type." eq "num " %then &amp;id. format=best12.;<BR />1234&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else &amp;id.;<BR />1235&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,order<BR />1236&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :varlist_&amp;i_type. separated by " ",<BR />1237&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :format_&amp;i_type. separated by " ",<BR />1238&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :idlist separated by " ",<BR />1239&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :idorder separated by " "<BR />1240&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %length(&amp;preloadfmt.) gt 0 %then from &amp;preloadfmt.;<BR />1241&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else from _for_format;<BR />1242&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; order by order<BR />1243&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1244&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let num_numlabels=&amp;sqlobs.;<BR />1245&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1246&nbsp;&nbsp;&nbsp; %end;<BR />1247&nbsp;&nbsp;&nbsp; quit;<BR />1248<BR />1249&nbsp;&nbsp;&nbsp; proc sql noprint;<BR />1250&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select distinct<BR />1251&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %let j_type=;<BR />1252&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do k=1 %to %sysfunc(countw(&amp;&amp;vars_all.));<BR />1253&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "&amp;j_type. "||cats("&amp;prefix.",<BR />1254<BR />1255&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;var_first.")) eq "NO" %then %do;<BR />1256&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; put(&amp;id.,&amp;format),"&amp;delimiter.",<BR />1257&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;use_varname.")) ne "NO" %then<BR />1258&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; scan("&amp;&amp;vars_all.",&amp;k.);<BR />1259&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )<BR />1260&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1261&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else %do;<BR />1262&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %sysfunc(upcase("&amp;use_varname.")) ne "NO" %then<BR />1263&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; scan("&amp;&amp;vars_all.",&amp;k.),;<BR />1264&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "&amp;delimiter.",put(&amp;id.,&amp;format))<BR />1265&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1266&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if &amp;k. lt %sysfunc(countw(&amp;&amp;vars_all.)) %then ||;<BR />1267&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else ,;<BR />1268&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1269&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; order<BR />1270&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into :varlist_all separated by " ",<BR />1271&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :idorder separated by " "<BR />1272&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %length(&amp;preloadfmt.) gt 0 %then from &amp;preloadfmt.;<BR />1273&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else from _for_format;<BR />1274&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; order by order<BR />1275&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1276&nbsp;&nbsp;&nbsp; quit;<BR />1277<BR />1278&nbsp; /*Create a format that will be used to assign values to the transposed variables*/<BR />1279&nbsp;&nbsp;&nbsp; data _for_format;<BR />1280&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %length(&amp;preloadfmt.) gt 0 %then set &amp;preloadfmt. (rename=(&amp;id.=start));<BR />1281&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else set _for_format&nbsp; (rename=(&amp;id.=start));<BR />1282&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1283&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if "&amp;tr_macro_type." eq "num " %then retain fmtname "labelfmt" type "N";<BR />1284&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else retain fmtname "$labelfmt" type "C";<BR />1285&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1286&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; label=<BR />1287&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %if %length(&amp;preloadfmt.) eq 0 %then _n_-1;<BR />1288&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %else order-1;<BR />1289&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1290&nbsp;&nbsp;&nbsp; run;<BR />1291<BR />1292&nbsp;&nbsp;&nbsp; proc format cntlin = _for_format;<BR />1293&nbsp;&nbsp;&nbsp; run ;<BR />1294<BR />1295&nbsp; /*Create and run the datastep that does the transposition*/<BR />1296&nbsp;&nbsp;&nbsp; data &amp;libname_out..&amp;out.;<BR />1297&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set &amp;libname_in..&amp;data. (keep=&amp;by. &amp;id.<BR />1298&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do i=1 %to %sysfunc(countw("&amp;vars_char."));<BR />1299&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %scan(&amp;vars_char.,&amp;i.)<BR />1300&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1301&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do i=1 %to %sysfunc(countw("&amp;vars_num."));<BR />1302&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %scan(&amp;vars_num.,&amp;i.)<BR />1303&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1304&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do i=1 %to %sysfunc(countw("&amp;to_copy."));<BR />1305&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %scan(&amp;to_copy.,&amp;i.)<BR />1306&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1307&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;dsoptions.<BR />1308&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; );<BR />1309&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; by &amp;by. &amp;notsorted.;<BR />1310&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;format_char. &amp;format_num.<BR />1311&nbsp;&nbsp;&nbsp; %if %length(&amp;vars_char.) gt 0 %then %do;<BR />1312&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; array want_char(*) $<BR />1313&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do i=1 %to %eval(&amp;num_numlabels.*%sysfunc(countw("&amp;vars_char.")));<BR />1314&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %scan(&amp;varlist_char.,&amp;i.)<BR />1315&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1316&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1317&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; array have_char(*) $ &amp;vars_char.;<BR />1318&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; retain want_char;<BR />1319&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if first.&amp;lastby. then call missing(of want_char(*));<BR />1320&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ___nchar=put(&amp;id.,labelfmt.)*dim(have_char);<BR />1321&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; do ___i=1 to dim(have_char);<BR />1322&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; want_char(___nchar+___i)=have_char(___i);<BR />1323&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end;<BR />1324&nbsp;&nbsp;&nbsp; %end;<BR />1325&nbsp;&nbsp;&nbsp; %if %length(&amp;vars_num.) gt 0 %then %do;<BR />1326&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; array want_num(*)<BR />1327&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %do i=1 %to %eval(&amp;num_numlabels.*%sysfunc(countw("&amp;vars_num.")));<BR />1328&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %scan(&amp;varlist_num.,&amp;i.)<BR />1329&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %end;<BR />1330&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;<BR />1331&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; array have_num(*) &amp;vars_num.;<BR />1332&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; retain want_num;<BR />1333&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if first.&amp;lastby. then call missing(of want_num(*));<BR />1334&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ___nnum=put(&amp;id.,labelfmt.)*dim(have_num);<BR />1335&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; do ___i=1 to dim(have_num);<BR />1336&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; want_num(___nnum+___i)=have_num(___i);<BR />1337&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end;<BR />1338&nbsp;&nbsp;&nbsp; %end;<BR />1339&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; drop &amp;id. ___: &amp;var. &amp;drop.;<BR />1340&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if last.&amp;lastby. then output;<BR />1341&nbsp;&nbsp;&nbsp; run;<BR />1342<BR />1343&nbsp;&nbsp;&nbsp; data &amp;libname_out..&amp;out.;<BR />1344&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; retain &amp;by. &amp;to_copy. &amp;varlist_all.;<BR />1345&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set &amp;libname_out..&amp;out.;<BR />1346&nbsp;&nbsp;&nbsp; run;<BR />1347<BR />1348&nbsp; /*Delete all temporary files*/<BR />1349&nbsp;&nbsp;&nbsp; proc delete data=work.t_e_m_p work._for_format;<BR />1350&nbsp;&nbsp;&nbsp; run;<BR />1351<BR />1352&nbsp; %mend transpose;<BR />1353&nbsp; options NOQUOTELENMAX;<BR />1354&nbsp; proc sort data=have out=need nodupkey;<BR />1355&nbsp;&nbsp;&nbsp; by time_m sym_root;<BR />1356&nbsp; run;<BR />1357<BR />1358&nbsp; %transpose(data=need, out=want, by=time_m, id=sym_root,<BR />1359&nbsp; delimiter=_,var=price size)</P> Sat, 27 May 2017 02:16:26 GMT https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362120#M64516 kbl 2017-05-27T02:16:26Z https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362121#M64517 <P>When your ran the code to first create the file have, did you check to see that it had actually run and created that file?</P> <P>&nbsp;</P> <P>Honestly, I don't know what the problem might be! My guess is that something you ran prior to running the macro that caused SAS to stall. Close SAS and then restart it. Then run the same code as you ran before .. including first running your data step to create the file have.</P> <P>&nbsp;</P> <P>Art, CEO, AnalystFinder.com</P> <P>&nbsp;</P> Sat, 27 May 2017 02:22:33 GMT https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362121#M64517 art297 2017-05-27T02:22:33Z https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362123#M64519 <P>Well, you were right.&nbsp; Once I shut down and restarted SAS, the file ran further.&nbsp; And you were also correct: there is an issue with the work file. This is the log error; how do I correct this?</P><P>&nbsp;</P><P>381&nbsp; %mend transpose;<BR />382&nbsp; options NOQUOTELENMAX;<BR />383&nbsp; proc sort data=have out=need nodupkey;<BR />ERROR: File WORK.HAVE.DATA does not exist.<BR />384&nbsp;&nbsp;&nbsp; by time_m sym_root;<BR />385&nbsp; run;<BR /><BR />NOTE: The SAS System stopped processing this step because of errors.<BR />WARNING: The data set WORK.NEED may be incomplete.&nbsp; When this step was stopped there were 0<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; observations and 0 variables.<BR />NOTE: PROCEDURE SORT used (Total process time):<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; real time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.06 seconds<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cpu time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.03 seconds<BR /><BR />386<BR />387&nbsp; %transpose(data=need, out=want, by=time_m, id=sym_root,<BR />388&nbsp; delimiter=_,var=price size)<BR /><BR /><BR />ERROR: The variable price in the DROP, KEEP, or RENAME list has never been referenced.<BR />ERROR: The variable size in the DROP, KEEP, or RENAME list has never been referenced.<BR /><BR />NOTE: The SAS System stopped processing this step because of errors.<BR />WARNING: The data set WORK.T_E_M_P may be incomplete.&nbsp; When this step was stopped there were 0<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; observations and 0 variables.<BR />NOTE: DATA statement used (Total process time):<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; real time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.03 seconds<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cpu time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.04 seconds<BR /><BR /><BR /></P> Sat, 27 May 2017 02:39:52 GMT https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362123#M64519 kbl 2017-05-27T02:39:52Z https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362126#M64521 <P>Just post the part of the log from the data step where you tried to create the file work.have</P> <P>&nbsp;</P> <P>Art, CEO, AnalystFinder.com</P> <P>&nbsp;</P> Sat, 27 May 2017 02:43:59 GMT https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362126#M64521 art297 2017-05-27T02:43:59Z https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362128#M64522 <P>Here &nbsp;%macro transpose(libname_in=,<BR />390&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; libname_out=,<BR />391&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; data= have,<BR />392&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; out= need,<BR />393&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; by=,<BR />you go Art; I really appreciate all of your help.</P><P>&nbsp;</P><P>&nbsp;</P> Sat, 27 May 2017 02:54:08 GMT https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362128#M64522 kbl 2017-05-27T02:54:08Z https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362130#M64524 <P>That is not what I need to see. I need to see the log from your running:</P> <P>&nbsp;</P> <PRE>Filename three 'G:\threeone.dat'; data have; infile three expandtabs; input TIME_M $ SYM_ROOT $ SIZE PRICE TIME2; run; proc sort data=have out=need nodupkey; by time_m sym_root; run; </PRE> <P>Art, CEO, AnalystFinder.com</P> Sat, 27 May 2017 02:58:54 GMT https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362130#M64524 art297 2017-05-27T02:58:54Z https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362131#M64525 <P>Here you go:</P><P>&nbsp;</P><P>77&nbsp; Filename three 'G:\threeone.dat';<BR />778&nbsp; data have;<BR />779&nbsp;&nbsp;&nbsp; infile&nbsp; three expandtabs;<BR />780&nbsp;&nbsp;&nbsp; input TIME_M $ SYM_ROOT $ SIZE PRICE TIME2;<BR />781&nbsp; run;<BR /><BR />NOTE: The infile THREE is:<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filename=G:\threeone.dat,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RECFM=V,LRECL=32767,File Size (bytes)=4860534,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Last Modified=26May2017:12:52:06,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Create Time=25May2017:23:18:31<BR /><BR />NOTE: 179219 records were read from the infile THREE.<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; The minimum record length was 19.<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; The maximum record length was 30.<BR />NOTE: The data set WORK.HAVE has 179219 observations and 5 variables.<BR />NOTE: DATA statement used (Total process time):<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; real time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.24 seconds<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cpu time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.17 seconds<BR /><BR /><BR />782<BR />783&nbsp; proc sort data=have out=need nodupkey;<BR />784&nbsp;&nbsp;&nbsp; by time_m sym_root;<BR />785&nbsp; run;<BR /><BR />NOTE: There were 179219 observations read from the data set WORK.HAVE.<BR />NOTE: 13934 observations with duplicate key values were deleted.<BR />NOTE: The data set WORK.NEED has 165285 observations and 5 variables.<BR />NOTE: PROCEDURE SORT used (Total process time):<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; real time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.14 seconds<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cpu time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.24 seconds<BR /><BR /><BR /></P> Sat, 27 May 2017 03:01:10 GMT https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362131#M64525 kbl 2017-05-27T03:01:10Z https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362132#M64526 <P>If you're still in the same session then just run:</P> <PRE>%transpose(data=need, out=want, by=time_m, id=sym_root, delimiter=_,var=price size)</PRE> <P>Art, CEO, AnalystFinder.com</P> Sat, 27 May 2017 03:05:38 GMT https://communities.sas.com/t5/SAS-Procedures/Transposed-data/m-p/362132#M64526 art297 2017-05-27T03:05:38Z