Proc import column names

[LRN]

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]

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

[LRN]

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]

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]

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]

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]

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]

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]

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]

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

[LRN]

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]

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 · Accepted solution]

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]

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]

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;

__________________________________________________