topic Re: Proc import column names in SAS Programming

Proc import column names

LRN — Wed, 12 Dec 2012 19:09:08 GMT

Hi,

I have to import many .xls files everymonth. (trying to automate it). The column numbers vary from month to month. While importing the column names are automatically assigned from a -z, aa-az, ba-bz like that. It is giving me problem while using sorting and calculation. I would like to know how can I assign the column names dynamically which can be used in sorting too.

Thanks

Re: Proc import column names

art297 — Wed, 12 Dec 2012 19:40:55 GMT

In your proc import have you been using

getnames=yes;

Re: Proc import column names

Linlin — Wed, 12 Dec 2012 20:03:42 GMT

Hi,

Is the link https://communities.sas.com/thread/32693

helpful to you?

Re: Proc import column names

LRN — Wed, 12 Dec 2012 21:12:42 GMT

Yes I did. Its picking up .xls column names. Thats why I am having problem. When sorting its sorting according to alphabet like aa, ab, ac, ...

Re: Proc import column names

LRN — Wed, 12 Dec 2012 21:17:18 GMT

Hi,

Thanks for the Link. I am going through that. Will post you how it worked out for me.

Re: Proc import column names

art297 — Wed, 12 Dec 2012 22:12:30 GMT

It sounds like your worksheet(s) don't have variable names. If so, and you use getnames=yes, then you are probably dropping the first row.

The link that Linlin provided may provide a solution, but I'm confused when you mention that "they don't sort correctly." We'd have to see your code, and how and why you are trying to sort the column names, to provide a better answer.

Re: Proc import column names

LRN — Thu, 13 Dec 2012 15:59:53 GMT

Hi,

I have more than 30 .xls files which needs to be imported automatically everymonth. The column numbers and names vary. The column numbers ranges from 240-250. The column names are not compliant to sas column names. So when I do import, sas assigns column names automatically which is from a-z, aa-az, ba-bz... like that. But because of this column names i am not able to use those in order. for example, if i want to use the column numbers for iteratice calculations.. But i am not able to do that.

Re: Proc import column names

novice — Thu, 13 Dec 2012 16:12:01 GMT

Hi LRN,

Are you saying that for each month the column numbers and names are different? For example

Month1: A1 B1 C1 D1

Month2: A1 D1

Re: Proc import column names

Tom — Thu, 13 Dec 2012 16:40:24 GMT

Why not generate an INFORMAT that can convert the default column names into numbers?

Or parse the name and generate a number.

A to Z would map to numbers 1 to 26

AA to AZ would map to 27 to 52

and so on.

data column ;

fmtname='COLUMN';

type='I';

letters = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ';

do i=0 to 26; do j=1 to 26 ;

label = i*26 + j ;

start = cats(substr(letters,i+1,1),substr(letters,j+1,1));

output;

end; end;

keep fmtname type start label ;

run;

proc format cntlin=column ;

run;

Re: Proc import column names

art297 — Thu, 13 Dec 2012 17:00:04 GMT

Another alternative would be to use arrays and macro variables. e.g., using Paige's suggested macro code:

%let dsn=jan;

%let ndays=3; /*should be 8 for you*/

/*create some test data*/

data &dsn.;

input aa ab ac ad ae af ag ah ai aj;

cards;

1 1 1 1 1 1 1 1 1 1

2 2 2 2 2 2 2 2 2 2

;

proc sql noprint;

select min(name),max(name),max(varnum)-1,(max(varnum)-1)/&ndays.

into :min,:max,:maxvar,:days

from dictionary.columns

where libname="WORK"

and memname=upcase("&dsn.")

;

quit;

data daily_tot_&dsn(keep=category total day:);

set &dsn.;

format total 29.; /* That's a very wide format */

array _in(*) &min.--&max.;

array day(&days.);

array _data(&maxvar.);

array _days(&ndays.);

category=_in(1);

do i=2 to dim(_in);

_data(i-1)=_in(i);

end;

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;

Re: Proc import column names

novice — Thu, 13 Dec 2012 17:13:12 GMT

Hi Tom,

Awesome code! I wish I can write such efficient code.

Re: Proc import column names

LRN — Thu, 13 Dec 2012 18:40:05 GMT

Hi,

Thank you so much.

I am working on this code.

Will post the result soon.

Re: Proc import column names

LRN — Thu, 13 Dec 2012 18:58:07 GMT

Me too.

Re: Proc import column names

LRN — Thu, 13 Dec 2012 19:00:49 GMT

Hi,

I am getting an error: All variables in array list must be the same type, i.e.., all numeric or character.

The variable 'Category' in the dataset is character. Rest all are numeric. Is it because of that?

Re: Proc import column names

art297 — Thu, 13 Dec 2012 19:59:30 GMT

That would definitely cause the error. All of the variables in an array must be of the same type. Try the following code instead:

%let dsn=jan;

%let ndays=3; /*should be 8 for you*/

/*create some test data*/

data &dsn.;

input aa $ ab ac ad ae af ag ah ai aj;

cards;

1 1 1 1 1 1 1 1 1 1

2 2 2 2 2 2 2 2 2 2

;

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.;

category=&first.;

format total 29.; /* That's a very wide format */

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;

Re: Proc import column names

LRN — Thu, 13 Dec 2012 20:20:17 GMT

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?

Re: Proc import column names

art297 — Thu, 13 Dec 2012 20:27:47 GMT

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

Re: Proc import column names

LRN — Thu, 13 Dec 2012 21:33:27 GMT

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

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

Re: Proc import column names

art297 — Thu, 13 Dec 2012 22:56:25 GMT

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;

Re: Proc import column names

Tom — Fri, 14 Dec 2012 01:51:20 GMT

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;