BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LRN
Calcite | Level 5 LRN
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

32 REPLIES 32
art297
Opal | Level 21

In your proc import have you been using

getnames=yes;

?

LRN
Calcite | Level 5 LRN
Calcite | Level 5

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
Lapis Lazuli | Level 10

Hi,

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

helpful to you?

LRN
Calcite | Level 5 LRN
Calcite | Level 5

Hi,

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

art297
Opal | Level 21

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
Calcite | Level 5 LRN
Calcite | Level 5

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
Calcite | Level 5

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
Super User Tom
Super User

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
Calcite | Level 5

Hi Tom,

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

LRN
Calcite | Level 5 LRN
Calcite | Level 5

Me too.

art297
Opal | Level 21

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
Calcite | Level 5 LRN
Calcite | Level 5

Hi,

Thank you so much.

I am working on this code.

Will post the result soon.

LRN
Calcite | Level 5 LRN
Calcite | Level 5

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
Opal | Level 21

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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