Proc import column names

[LRN]

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

[art297]

In your proc import have you been using

getnames=yes;

?

[LRN]

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

[Linlin]

Hi,

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

helpful to you?

[LRN]

Hi,

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

[art297]

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.

[LRN]

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.

[novice]

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

[Tom]

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;

[novice]

Hi Tom,

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

[LRN]

Me too.

[art297]

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;

[LRN]

Hi,

Thank you so much.

I am working on this code.

Will post the result soon.

[LRN]

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?

[art297]

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;