Solved: Re: Proc import column names - Page 2

LRN · Posted 12-13-2012 03:20 PM

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?

art297 · Posted 12-13-2012 03:27 PM

Did you change the 3 to an 8 in the 2nd let statement. post your log

LRN · Posted 12-13-2012 04:33 PM

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

art297 · Posted 12-13-2012 05:56 PM

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;

LRN · Posted 12-17-2012 02:00 PM

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

Tom · Posted 12-13-2012 08:51 PM

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;

LRN · Posted 12-17-2012 02:12 PM

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;

art297 · Posted 12-17-2012 02:35 PM

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;

LRN · Posted 12-17-2012 02:46 PM

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.

LRN · Posted 12-17-2012 02:50 PM

I don't have to keep the first variable name. I can rename it so that it will be in order.

LRN · Posted 12-17-2012 03:46 PM

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.

LRN · Posted 12-17-2012 05:05 PM

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;

__________________________________________________

art297 · Posted 12-17-2012 05:19 PM

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.

LRN · Posted 12-18-2012 09:41 AM

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;

LRN · Posted 12-17-2012 05:05 PM

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;

__________________________________________________

SAS Innovate 2025: Save the Date

SAS Training: Just a Click Away