Proc import column names

Accepted Solution Solved
Reply
Contributor LRN
Contributor
Posts: 57
Accepted Solution

Proc import column names

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


Accepted Solutions
Solution
‎12-17-2012 05:19 PM
PROC Star
Posts: 7,492

Re: Proc import column names

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.

View solution in original post


All Replies
PROC Star
Posts: 7,492

Re: Proc import column names

In your proc import have you been using

getnames=yes;

?

Contributor LRN
Contributor
Posts: 57

Re: Proc import column names

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

Super Contributor
Posts: 1,636

Re: Proc import column names

Hi,

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

helpful to you?

Contributor LRN
Contributor
Posts: 57

Re: Proc import column names

Hi,

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

PROC Star
Posts: 7,492

Re: Proc import column names

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.

Contributor LRN
Contributor
Posts: 57

Re: Proc import column names

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.

Occasional Contributor
Posts: 6

Re: Proc import column names

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

Super User
Super User
Posts: 7,083

Re: Proc import column names

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;


Occasional Contributor
Posts: 6

Re: Proc import column names

Hi Tom,

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

Contributor LRN
Contributor
Posts: 57

Re: Proc import column names

Me too.

PROC Star
Posts: 7,492

Re: Proc import column names

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 daySmiley Happy;

  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;

Contributor LRN
Contributor
Posts: 57

Re: Proc import column names

Hi,

Thank you so much.

I am working on this code.

Will post the result soon.

Contributor LRN
Contributor
Posts: 57

Re: Proc import column names

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?

PROC Star
Posts: 7,492

Re: Proc import column names

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 daySmiley Happy;

  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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 32 replies
  • 2485 views
  • 9 likes
  • 6 in conversation