Thank you so much.
But now, I am getting the following error:
ERROR: Array subscript out of range at line 57 column 16.
What does it mean? How can I fix it?
Did you change the 3 to an 8 in the 2nd let statement. post your log
HI,
Below is my log.
Thank you
1 | ;*';*";*/;quit;run; |
2 | OPTIONS PAGENO=MIN; |
3 | %LET _CLIENTTASKLABEL='Column names'; |
4 | %LET _CLIENTPROJECTPATH=''; |
5 | %LET _CLIENTPROJECTNAME=''; |
6 | %LET _SASPROGRAMFILE=; |
7 | |
8 | ODS _ALL_ CLOSE; |
9 | OPTIONS DEV=ACTIVEX; |
NOTE: Procedures may not support all options or statements for all devices. For details, see the documentation for each procedure.
10 | GOPTIONS XPIXELS=0 YPIXELS=0; |
11 | FILENAME EGSR TEMP; |
12 | ODS tagsets.sasreport12(ID=EGSR) FILE=EGSR STYLE=Analysis |
12 | ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS/EnterpriseGuide/4.3/Styles/Analysis.css") NOGTITLE NOGFOOTNOTE |
12 | ! GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on"); |
NOTE: Writing TAGSETS.SASREPORT12(EGSR) Body file: EGSR
13 | |
14 | GOPTIONS ACCESSIBLE; |
15 | |
16 | %let ndays=8; |
17 | %let dsn=in_nyma_protocol; |
18 | |
19 | proc sql; |
20 | |
21 | select max(name),max(varnum)-1,(max(varnum)-1)/&ndays. |
22 | |
23 | into :max,:maxvar,:days |
24 | |
25 | from dictionary.columns |
26 | |
27 | where libname="WORK" |
28 | |
29 | and memname=upcase("&dsn.") |
30 | |
31 | ; |
32 | quit; |
NOTE: PROCEDURE SQL used (Total process time):
real time | 0.03 seconds | |
cpu time | 0.01 seconds | |
33 | |
34 | proc sql; |
35 | select name into: min |
36 | from dictionary.columns |
37 | where varnum=2 |
38 | and libname="WORK" |
39 | and memname=upcase("&dsn."); |
40 | quit; |
NOTE: PROCEDURE SQL used (Total process time):
real time | 0.00 seconds | |
cpu time | 0.01 seconds | |
41 | ||
42 | proc sql; | |
43 | select name into: first | |
2 | The SAS System | 16:12 Thursday, December 13, 2012 |
44 | from dictionary.columns |
45 | where varnum=1 |
46 | and libname="WORK" |
47 | and memname=upcase("&dsn."); |
48 | quit; |
NOTE: PROCEDURE SQL used (Total process time):
real time | 0.00 seconds | |
cpu time | 0.01 seconds | |
49 | |
50 | |
51 | data daily_tot_&dsn(keep=category total day:); |
52 | length category $25.; |
53 | |
54 | set &dsn.; |
55 | |
56 | category="&first."; |
57 | |
58 | format total 20.; /* That's a very wide format */ |
59 | |
60 | array _data(*) &min.--&max.; |
61 | |
62 | array day(&days.); |
NOTE: The array day has the same name as a SAS-supplied or user-defined function. Parentheses following this name are treated as
array references and not function references. | |
63 | |
64 | array _days(&ndays.); |
65 | |
66 | total=sum(of _data(*)); |
67 | |
68 | do i=1 to &days.; |
69 | |
70 | do j=1 to &ndays.; |
71 | |
72 | _days(j)=_data((i-1)*&ndays.+j); |
73 | |
74 | end; |
75 | |
76 | day(i)=sum(of _days(*)); |
77 | |
78 | end; |
79 | |
80 | run; |
ERROR: Array subscript out of range at line 72 column 16.
category=Protocol Category Protocol Category=Video B=122647032941 C=54675955798 D=52066307515 E=101209044870 F=134881115583
G=150603045003 H=172293562037 I=4 J=2 K=49577681926 L=49148686462 M=91596235003 N=124109796864 O=140462242031 P=161878461529
Q=175455333978 R=121743611366 S=50247366599 T=50002968607 U=92345472695 V=122736220635 W=137634445212 X=157649236329 Y=178651619684
Z=149406571740 AA=80630068566 AB=79922981557 AC=153824220113 AD=191624911120 AE=192249577630 AF=204256671322 AG=218843557770
AH=165509846590 AI=70914049858 AJ=69076526076 AK=131552575714 AL=180694817062 AM=205379617009 AN=240159525380 AO=261431333950
AP=178266197872 AQ=74567186302 AR=73893030899 AS=135286175791 AT=181182337554 AU=204912826439 AV=234860636307 AW=256486948480
AX=185860908317 AY=80880175929 AZ=78620897685 BA=151406363775 BB=204030015083 BC=223426989833 BD=233982089985 BE=281714753144
BF=193728594752 BG=70236475534 BH=67872650556 BI=158472163529 BJ=210460299653 BK=233600228809 BL=256655785674 BM=295411465316
BN=177239529442 BO=60484324934 BP=65329551371 BQ=133568162159 BR=184338207260 BS=208978270612 BT=240182118780 BU=285215215616
BV=172623066469 BW=57163310448 BX=66585662371 BY=132400415826 BZ=178331593056 CA=203057837420 CB=236385386987 CC=283720096599
CD=172188847846 CE=59045304241 CF=66320192358 CG=132493492974 CH=179610062717 CI=204372646397 CJ=240325103817 CK=286275804278
CL=170313523203 CM=58869895184 CN=67333337781 CO=134425906509 CP=181055409448 CQ=203851133061 CR=236840545425 CS=283235387046
3 | The SAS System | 16:12 Thursday, December 13, 2012 |
CT=172467160869 CU=61019421818 CV=69203886615 CW=138612161420 CX=184175755018 CY=206820274380 CZ=232979096284 DA=283217322671
DB=182712231315 DC=67283243742 DD=73577563002 DE=161176709170 DF=203284742534 DG=213258329989 DH=226431010427 DI=280425609345
DJ=191892527861 DK=71470102951 DL=70180041117 DM=166499935801 DN=224904416924 DO=257560447651 DP=287496232856 DQ=319792791021
DR=182110269213 DS=61679483117 DT=68989677071 DU=138250034848 DV=190639225728 DW=218013723186 DX=248926478007 DY=298238862353
DZ=176625981549 EA=61039307171 EB=69306683421 EC=138390567475 ED=189497551447 EE=215040594126 EF=246658139890 EG=296506243768
EH=174822741583 EI=61039682228 EJ=69723454829 EK=139289186949 EL=193996632818 EM=239274382691 EN=292932072937 EO=318829511654
EP=176531471703 EQ=61747731420 ER=69360381175 ES=137906855220 ET=190165380312 EU=216590737417 EV=250615090051 EW=298193091398
EX=174549351791 EY=66502676036 EZ=71940761929 FA=150479493643 FB=211101470039 FC=238117135808 FD=261248324557 FE=295760062713
FF=186663253979 FG=70572524743 FH=72686322239 FI=152379870369 FJ=188399786168 FK=198936803137 FL=216705761858 FM=271413966667
FN=187235761755 FO=71934572848 FP=68169707509 FQ=152395877533 FR=199444038899 FS=221170565779 FT=246836996782 FU=292649991138
FV=176664218961 FW=63266966406 FX=65847592835 FY=135496744753 FZ=188564058909 GA=222176609981 GB=256324609738 GC=298803748584
GD=160395703456 GE=55644132883 GF=68347571792 GG=134484410958 GH=181959381652 GI=204419944174 GJ=240370545662 GK=287402074456
GL=169367675973 GM=60272935006 GN=67363583385 GO=136841639222 GP=181083755234 GQ=205533312999 GR=245154330258 GS=296605717388
GT=172374844455 GU=61901218544 GV=70620549947 GW=148168310085 GX=194631723598 GY=219069949700 GZ=262816621245 HA=313184062395
HB=175241193615 HC=64232603634 HD=73111954287 HE=142818922959 HF=189147081926 HG=212697057784 HH=220201416094 HI=258799303452
HJ=180568051370 HK=69198995143 HL=73834956707 HM=164988824271 HN=216921424067 HO=243211216798 HP=263593308434 HQ=291358060613
HR=194018290191 HS=74062417075 HT=71320453865 HU=167378353608 HV=224908698851 HW=249683463137 HX=264030352378 HY=292117742619
HZ=162935185617 IA=56357432798 IB=66356078535 IC=135493339383 ID=185730892177 IE=209451227527 IF=241612094065 IG=287842715273
IH=177581033545 II=63929112027 IJ=67824359823 IK=136877365745 IL=189099724899 IM=212904807166 IN=240260158974 IO=283287603329
total=2938132110419 day1=788376063748 day2=792228437794 day3=911010941127 day4=. day5=. day6=. day7=. day8=. day9=. day10=. day11=.
day12=. day13=. day14=. day15=. day16=. day17=. day18=. day19=. day20=. day21=. day22=. day23=. day24=. day25=. day26=. day27=.
day28=. day29=. day30=. day31=. _days1=149406571740 _days2=50247366599 _days3=50002968607 _days4=92345472695 _days5=122736220635
_days6=137634445212 _days7=157649236329 _days8=178651619684 _ERROR_=1 _N_=1
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 1 observations read from the data set WORK.IN_NYMA_PROTOCOL.
WARNING: The data set WORK.DAILY_TOT_IN_NYMA_PROTOCOL may be incomplete. When this step was stopped there were 0 observations and
33 variables. |
WARNING: Data set WORK.DAILY_TOT_IN_NYMA_PROTOCOL was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time | 0.01 seconds | |
cpu time | 0.01 seconds | |
81 | ||
82 | /*data daily_tot_&dsn(keep=category total day:);*/ | |
83 | /**/ | |
84 | /* set &dsn.;*/ | |
85 | /**/ | |
86 | /* format total 12.; /* That's a very wide format */*/ | |
87 | /**/ | |
88 | /* array _in(*) &min.-- &max.;*/ | |
89 | /**/ | |
90 | /* array day(&days.);*/ | |
91 | /**/ | |
92 | /* array _data(&maxvar.);*/ | |
93 | /**/ | |
94 | /* array _days(&ndays.);*/ | |
95 | /**/ | |
96 | /*/* category=_in(1);*/*/ | |
97 | /**/ | |
98 | /* do i=2 to dim(_in);*/ | |
99 | /**/ | |
100 | /* | _data(i-1)=_in(i);*/ |
101 | /**/ | |
102 | /* end;*/ | |
103 | /**/ | |
104 | /* total=sum(of _data(*));*/ | |
105 | /**/ | |
4 | The SAS System | 16:12 Thursday, December 13, 2012 |
106 | /* do i=1 to &days.;*/ | |
107 | /**/ | |
108 | /* | do j=1 to &ndays.;*/ |
109 | /**/ | |
110 | /* | _days(j)=_data((i-1)*&ndays.+j);*/ |
111 | /**/ | |
112 | /* | end;*/ |
113 | /**/ | |
114 | /* | day(i)=sum(of _days(*));*/ |
115 | /**/ | |
116 | /* end;*/ | |
117 | /**/ | |
118 | /*run;*/ | |
119 | ||
120 | GOPTIONS NOACCESSIBLE; | |
121 | %LET _CLIENTTASKLABEL=; | |
122 | %LET _CLIENTPROJECTPATH=; | |
123 | %LET _CLIENTPROJECTNAME=; | |
124 | %LET _SASPROGRAMFILE=; | |
125 | ||
126 | ;*';*";*/;quit;run; | |
127 | ODS _ALL_ CLOSE; | |
128 | ||
129 | ||
130 | QUIT; RUN; | |
131 |
You changed the code I had suggested and left out some critical values. Try the following (you can add your various let and ods statements, if needed, before and after the code):
%let dsn=in_nyma_protocol;
%let ndays=8;
proc sql noprint;
select min(name),max(name),max(varnum)-1,(max(varnum)-1)/&ndays.
into :first,:max,:maxvar,:days
from dictionary.columns
where libname="WORK"
and memname=upcase("&dsn.")
;
select name
into :min
from dictionary.columns
where libname="WORK"
and memname=upcase("&dsn.")
and varnum=2
;
quit;
data daily_tot_&dsn(keep=category total day:);
set &dsn.;
length category $25;
category=&first.;
format total 20.;
array _data(*) &min.--&max.;
array day(&days.);
array _days(&ndays.);
total=sum(of _data(*));
do i=1 to &days.;
do j=1 to &ndays.;
_days(j)=_data((i-1)*&ndays.+j);
end;
day(i)=sum(of _days(*));
end;
run;
Hi,
Thank you.
I changed the code for &first. Because it was resolving to AA (which is not the first column).
But still I am getting the error : Array subscript out of range at line.... column....
Attached is the Log: (sorry don't find the option to attach as a file).
1 | ;*';*";*/;quit;run; |
2 | OPTIONS PAGENO=MIN; |
3 | %LET _CLIENTTASKLABEL='Column names'; |
4 | %LET _CLIENTPROJECTPATH=''; |
5 | %LET _CLIENTPROJECTNAME=''; |
6 | %LET _SASPROGRAMFILE=; |
7 | |
8 | ODS _ALL_ CLOSE; |
9 | OPTIONS DEV=ACTIVEX; |
NOTE: Procedures may not support all options or statements for all devices. For details, see the documentation for each procedure.
10 | GOPTIONS XPIXELS=0 YPIXELS=0; |
11 | FILENAME EGSR TEMP; |
12 | ODS tagsets.sasreport12(ID=EGSR) FILE=EGSR STYLE=Analysis |
12 | ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS/EnterpriseGuide/4.3/Styles/Analysis.css") NOGTITLE NOGFOOTNOTE |
12 | ! GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on"); |
SYMBOLGEN: Macro variable SASWORKLOCATION resolves to
"/saswc/work/SAS_workD548000030D7_sas-primary/SAS_work67AC000030D7_sas-primary/" |
NOTE: Writing TAGSETS.SASREPORT12(EGSR) Body file: EGSR
13 | |
14 | GOPTIONS ACCESSIBLE; |
15 | |
16 | %let dsn=in_nyma_protocol; |
17 | |
18 | %let ndays=8; |
19 | |
20 | |
21 | |
22 | proc sql noprint; |
23 | |
24 | select min(name),max(name),max(varnum)-1,(max(varnum)-1)/&ndays. |
SYMBOLGEN: Macro variable NDAYS resolves to 8
25 | |
26 | into :first,:max,:maxvar,:days |
27 | |
28 | from dictionary.columns |
29 | |
30 | where libname="WORK" |
31 | |
32 | and memname=upcase("&dsn.") |
SYMBOLGEN: Macro variable DSN resolves to in_nyma_protocol
33 | |
34 | ; |
35 | |
36 | |
37 | |
38 | select name |
39 | |
40 | into :min |
41 | |
42 | from dictionary.columns |
43 | |
44 | where libname="WORK" |
45 | |
46 | and memname=upcase("&dsn.") |
SYMBOLGEN: Macro variable DSN resolves to in_nyma_protocol
47 | ||
48 | and varnum=2 | |
2 | The SAS System | 11:50 Monday, December 17, 2012 |
49 | |
50 | ; |
51 | |
52 | quit; |
NOTE: PROCEDURE SQL used (Total process time):
real time | 0.00 seconds | |
cpu time | 0.02 seconds | |
53 | |
54 | |
55 |
SYMBOLGEN: Macro variable DSN resolves to in_nyma_protocol
56 | data daily_tot_&dsn(keep=category total day:); |
57 |
SYMBOLGEN: Macro variable DSN resolves to in_nyma_protocol
58 | set &dsn.; |
59 | |
60 | length category $25; |
61 | |
62 | category=&first.; |
SYMBOLGEN: Macro variable FIRST resolves to AA | |
63 | |
64 | format total 20.; |
65 | |
66 | array _data(*) &min.--&max.; |
SYMBOLGEN: Macro variable MIN resolves to B | |
SYMBOLGEN: Macro variable MAX resolves to Z | |
67 | |
68 | array day(&days.); |
NOTE: The array day has the same name as a SAS-supplied or user-defined function. Parentheses following this name are treated as
array references and not function references. | |
SYMBOLGEN: Macro variable DAYS resolves to | 31 |
69 | |
70 | array _days(&ndays.); |
SYMBOLGEN: Macro variable NDAYS resolves to 8
71 | |
72 | total=sum(of _data(*)); |
73 | |
74 | do i=1 to &days.; |
SYMBOLGEN: Macro variable DAYS resolves to | 31 |
75 | |
76 | do j=1 to &ndays.; |
SYMBOLGEN: Macro variable NDAYS resolves to 8
77 | |
78 | _days(j)=_data((i-1)*&ndays.+j); |
SYMBOLGEN: Macro variable NDAYS resolves to 8
79 | |
80 | end; |
81 | |
82 | day(i)=sum(of _days(*)); |
83 | |
84 | end; |
85 | |
86 | run; |
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
62:1 | ||
3 | The SAS System | 11:50 Monday, December 17, 2012 |
ERROR: Array subscript out of range at line 78 column 16.
Protocol Category=Video B=122647032941 C=54675955798 D=52066307515 E=101209044870 F=134881115583 G=150603045003 H=172293562037 I=4
J=2 K=49577681926 L=49148686462 M=91596235003 N=124109796864 O=140462242031 P=161878461529 Q=175455333978 R=121743611366
S=50247366599 T=50002968607 U=92345472695 V=122736220635 W=137634445212 X=157649236329 Y=178651619684 Z=149406571740 AA=80630068566
AB=79922981557 AC=153824220113 AD=191624911120 AE=192249577630 AF=204256671322 AG=218843557770 AH=165509846590 AI=70914049858
AJ=69076526076 AK=131552575714 AL=180694817062 AM=205379617009 AN=240159525380 AO=261431333950 AP=178266197872 AQ=74567186302
AR=73893030899 AS=135286175791 AT=181182337554 AU=204912826439 AV=234860636307 AW=256486948480 AX=185860908317 AY=80880175929
AZ=78620897685 BA=151406363775 BB=204030015083 BC=223426989833 BD=233982089985 BE=281714753144 BF=193728594752 BG=70236475534
BH=67872650556 BI=158472163529 BJ=210460299653 BK=233600228809 BL=256655785674 BM=295411465316 BN=177239529442 BO=60484324934
BP=65329551371 BQ=133568162159 BR=184338207260 BS=208978270612 BT=240182118780 BU=285215215616 BV=172623066469 BW=57163310448
BX=66585662371 BY=132400415826 BZ=178331593056 CA=203057837420 CB=236385386987 CC=283720096599 CD=172188847846 CE=59045304241
CF=66320192358 CG=132493492974 CH=179610062717 CI=204372646397 CJ=240325103817 CK=286275804278 CL=170313523203 CM=58869895184
CN=67333337781 CO=134425906509 CP=181055409448 CQ=203851133061 CR=236840545425 CS=283235387046 CT=172467160869 CU=61019421818
CV=69203886615 CW=138612161420 CX=184175755018 CY=206820274380 CZ=232979096284 DA=283217322671 DB=182712231315 DC=67283243742
DD=73577563002 DE=161176709170 DF=203284742534 DG=213258329989 DH=226431010427 DI=280425609345 DJ=191892527861 DK=71470102951
DL=70180041117 DM=166499935801 DN=224904416924 DO=257560447651 DP=287496232856 DQ=319792791021 DR=182110269213 DS=61679483117
DT=68989677071 DU=138250034848 DV=190639225728 DW=218013723186 DX=248926478007 DY=298238862353 DZ=176625981549 EA=61039307171
EB=69306683421 EC=138390567475 ED=189497551447 EE=215040594126 EF=246658139890 EG=296506243768 EH=174822741583 EI=61039682228
EJ=69723454829 EK=139289186949 EL=193996632818 EM=239274382691 EN=292932072937 EO=318829511654 EP=176531471703 EQ=61747731420
ER=69360381175 ES=137906855220 ET=190165380312 EU=216590737417 EV=250615090051 EW=298193091398 EX=174549351791 EY=66502676036
EZ=71940761929 FA=150479493643 FB=211101470039 FC=238117135808 FD=261248324557 FE=295760062713 FF=186663253979 FG=70572524743
FH=72686322239 FI=152379870369 FJ=188399786168 FK=198936803137 FL=216705761858 FM=271413966667 FN=187235761755 FO=71934572848
FP=68169707509 FQ=152395877533 FR=199444038899 FS=221170565779 FT=246836996782 FU=292649991138 FV=176664218961 FW=63266966406
FX=65847592835 FY=135496744753 FZ=188564058909 GA=222176609981 GB=256324609738 GC=298803748584 GD=160395703456 GE=55644132883
GF=68347571792 GG=134484410958 GH=181959381652 GI=204419944174 GJ=240370545662 GK=287402074456 GL=169367675973 GM=60272935006
GN=67363583385 GO=136841639222 GP=181083755234 GQ=205533312999 GR=245154330258 GS=296605717388 GT=172374844455 GU=61901218544
GV=70620549947 GW=148168310085 GX=194631723598 GY=219069949700 GZ=262816621245 HA=313184062395 HB=175241193615 HC=64232603634
HD=73111954287 HE=142818922959 HF=189147081926 HG=212697057784 HH=220201416094 HI=258799303452 HJ=180568051370 HK=69198995143
HL=73834956707 HM=164988824271 HN=216921424067 HO=243211216798 HP=263593308434 HQ=291358060613 HR=194018290191 HS=74062417075
HT=71320453865 HU=167378353608 HV=224908698851 HW=249683463137 HX=264030352378 HY=292117742619 HZ=162935185617 IA=56357432798
IB=66356078535 IC=135493339383 ID=185730892177 IE=209451227527 IF=241612094065 IG=287842715273 IH=177581033545 II=63929112027
IJ=67824359823 IK=136877365745 IL=189099724899 IM=212904807166 IN=240260158974 IO=283287603329 category=80630068566
total=2938132110419 day1=788376063748 day2=792228437794 day3=911010941127 day4=. day5=. day6=. day7=. day8=. day9=. day10=. day11=.
day12=. day13=. day14=. day15=. day16=. day17=. day18=. day19=. day20=. day21=. day22=. day23=. day24=. day25=. day26=. day27=.
day28=. day29=. day30=. day31=. _days1=149406571740 _days2=50247366599 _days3=50002968607 _days4=92345472695 _days5=122736220635
_days6=137634445212 _days7=157649236329 _days8=178651619684 _ERROR_=1 _N_=1
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 1 observations read from the data set WORK.IN_NYMA_PROTOCOL.
WARNING: The data set WORK.DAILY_TOT_IN_NYMA_PROTOCOL may be incomplete. When this step was stopped there were 0 observations and
33 variables. |
WARNING: Data set WORK.DAILY_TOT_IN_NYMA_PROTOCOL was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time | 0.00 seconds | |
cpu time | 0.01 seconds | |
87 | ||
88 | GOPTIONS NOACCESSIBLE; | |
89 | %LET _CLIENTTASKLABEL=; | |
90 | %LET _CLIENTPROJECTPATH=; | |
91 | %LET _CLIENTPROJECTNAME=; | |
92 | %LET _SASPROGRAMFILE=; | |
93 | ||
94 | ;*';*";*/;quit;run; | |
95 | ODS _ALL_ CLOSE; | |
96 | ||
97 | ||
98 | QUIT; RUN; | |
4 | The SAS System | 11:50 Monday, December 17, 2012 |
99 |
Why not just query DICTIONARY.COLUMNS to generate rename pairs? You can use the VARNUM to generate the new name.
This will rename A...Z, AA...AZ to VAR001 - VAR052 .
data have ;
length a b c d e f g h i j k l m n o p q r s t u v w x y z
aa ab ac ad ae af ag ah ai aj ak al am an ao ap aq ar as at au av aw ax ay az
8 ;
array _x _all_;
stop;
run;
proc sql noprint;
select catx('=',name,'VAR'||put(varnum,z3.))
into :rename separated by ' '
from dictionary.columns
where libname='WORK'
and memname='HAVE'
;
quit;
data want ;
set have (rename=(&rename));
run;
proc contents data=want;
run;
Hi,
Thank you for the code.
It is giving me the following error. I am not sure if it is because of the space in between the two words 'protocol category'.
Thank you
HQ=VAR225 HR=VAR226 HS=VAR227 HT=VAR228 HU=VAR229 HV=VAR230 HW=VAR231 HX=VAR232 HY=VAR233 HZ=VAR234 IA=VAR235 IB=VAR236
IC=VAR237 ID=VAR238 IE=VAR239 IF=VAR240 IG=VAR241 IH=VAR242 II=VAR243 IJ=VAR244 IK=VAR245 IL=VAR246 IM=VAR247 IN=VAR248
IO=VAR249
NOTE: Line generated by the macro variable "RENAME".
46 Protocol Category=VAR001 B=VAR002 C=VAR003 D=VAR004 E=VAR005 F=VAR006 G=VAR007 H=VAR008 I=VAR009 J=VAR010 K=VAR011
________
79
46 ! L=VAR012 M=VAR013 N=VAR014 O=VAR015 P=VAR016 Q=VAR017 R=VAR018 S=VAR019 T=VAR020 U=VAR021 V=VAR022 W=VAR023 X=VAR024
46 ! Y=VAR025 Z=VAR026
ERROR 79-322: Expecting a =.
47
48 run;
This continues to get interesting. Not sure how you got a variable called Protcol Category, unless you had imported the file with the validvarname=any system option.
Regardless, since you weren't interested in renaming that variable anyway, you can just skip it when creating the rename macro variable. e.g.:
proc sql noprint;
select catx('=',name,'VAR'||put(varnum,z3.))
into :rename separated by ' '
from dictionary.columns
where libname='WORK'
and memname='HAVE'
and upcase(name) ne "PROTOCOL CATEGORY"
;
quit;
data want ;
set have (rename=(&rename));
run;
Hi,
Sorry I think I missed few things. The first variable might have any names like 'protocol category', category, etc..only from variable 2, since the column names are not sas compatible, sas names it automatically.
I don't have to keep the first variable name. I can rename it so that it will be in order.
Hi,
I am getting the following errors. I don't have to keep the first variable name as such since it will be different in each dataset.
Thank you
13 | |
14 | GOPTIONS ACCESSIBLE; |
15 | data have ; |
16 | |
17 | set in_nyma_protocol; |
18 | |
19 | array _x _all_; |
ERROR: All variables in array list must be the same type, i.e., all numeric or character.
ERROR: All variables in array list must be the same type, i.e., all numeric or character.........
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.HAVE may be incomplete. When this step was stopped there were 0 observations and 249 variables.
WARNING: Data set WORK.HAVE was not replaced because this step was stopped.
Hi,
When I try these 2 programs, it works perfect. Except that, I am not able to use '&first'. My Problem is: All the dataset has different first variable names. It might be either one word or two words. How can I solve that.
Thanks for all your help.
Program 1.
----------------------------------------------------------------------------------------
data have ;
set in_nyma_protocol;
array _x _numeric_;
run;
proc sql noprint;
select catx('=',name,'VAR'||put(varnum,z3.))
into :rename separated by ' '
from dictionary.columns
where libname='WORK'
and memname='HAVE'
and varnum ne 1;
;
quit;
data want ;
set have (rename=(&rename));
run;
-------------------------------------------------------------------------------------
Program 2
---------------------------------------------------------------------------------
%let dsn=want;
%let ndays=8;
proc sql noprint;
select min(name), max(name),max(varnum)-1,(max(varnum)-1)/&ndays.
into :first, :max,:maxvar,:days
from dictionary.columns
where libname="WORK"
and memname=upcase("&dsn.")
;
select name
into :min
from dictionary.columns
where libname="WORK"
and memname=upcase("&dsn.")
and varnum=2
;
quit;
data daily_tot_&dsn(keep= total day:);
set &dsn.;
/* length category $25;
category=&first.;
*/
format total 20.;
array _data(*) &min.--&max.;
array day(&days.);
array _days(&ndays.);
total=sum(of _data(*));
do i=1 to &days.;
do j=1 to &ndays.;
_days(j)=_data((i-1)*&ndays.+j);
end;
day(i)=sum(of _days(*));
end;
run;
__________________________________________________
Can you redo the import using getnames=no ???? or, minimally, redo the import with the SAS system option set to:
validvarname=v7;
Those would be two ways of eliminating the problem of the first variable having an odd name.
I'm not sure what macro variable you are referring to as &first.
Hi,
Thanks a lot.
I just changed the proc import as follows: It worked great. Thanks once again
PROC IMPORT
DATAFILE= &testfile
OUT=&outdsn
REPLACE
DBMS=XLS;
SHEET="Sheet1";
GETNAMES=no;
datarow=2;
RUN;
Hi,
When I try these 2 programs, it works perfect. Except that, I am not able to use '&first'. My Problem is: All the dataset has different first variable names. It might be either one word or two words. How can I solve that.
Thanks for all your help.
Program 1.
----------------------------------------------------------------------------------------
data have ;
set in_nyma_protocol;
array _x _numeric_;
run;
proc sql noprint;
select catx('=',name,'VAR'||put(varnum,z3.))
into :rename separated by ' '
from dictionary.columns
where libname='WORK'
and memname='HAVE'
and varnum ne 1;
;
quit;
data want ;
set have (rename=(&rename));
run;
-------------------------------------------------------------------------------------
Program 2
---------------------------------------------------------------------------------
%let dsn=want;
%let ndays=8;
proc sql noprint;
select min(name), max(name),max(varnum)-1,(max(varnum)-1)/&ndays.
into :first, :max,:maxvar,:days
from dictionary.columns
where libname="WORK"
and memname=upcase("&dsn.")
;
select name
into :min
from dictionary.columns
where libname="WORK"
and memname=upcase("&dsn.")
and varnum=2
;
quit;
data daily_tot_&dsn(keep= total day:);
set &dsn.;
/* length category $25;
category=&first.;
*/
format total 20.;
array _data(*) &min.--&max.;
array day(&days.);
array _days(&ndays.);
total=sum(of _data(*));
do i=1 to &days.;
do j=1 to &ndays.;
_days(j)=_data((i-1)*&ndays.+j);
end;
day(i)=sum(of _days(*));
end;
run;
__________________________________________________
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.