Alternate columns in a dataset

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

Alternate columns in a dataset

Thanks in advance for any help anyone can provide.

I have two datasets where I have previously used the prefix option to designate the variable content in each set.

Ex dataset 1:

giveunit1

giveunit2

giveunit3

giveunit4

Ex dataset2:

givecust1

givecust2

givecust3

givecust4

I have joined the two datasets together. However, the variables come in as:

id     date     giveunit1     giveunit2     giveunit3     giveunit4     givecust1     givecust2     givecust3     givecut4

In the final dataset I need to show the data as:

id     date     giveunit1     givecust1     giveunit2     givecust2     giveunit3     givecust3     giveunit4     givecust4

One issue is that I never know how many columns I may have on each run so I cannot specify them by name because at the time of execution I may have specified a column that many not exist (or conversely not explicitly specify a column that I may now have) causing execution to fail or to deliver incomplete data.

Does anyone know if there is a way to alternate columns?

Thank you so much for any help anyone may have.

Jeff


Accepted Solutions
Solution
‎06-19-2013 05:23 AM
Occasional Contributor
Posts: 18

Re: Alternate columns in a dataset

Hi Jeff-DOC,

Please try this code I am sure this will meet your requirement.

Please Drop your feedback and mark answer as correct if your purpose is solved.


proc sql;

create table name_1_secq as

  select name as name_1, input(substr(name,9,(length(name)-8)),2.) as secq_1

  from dictionary.columns

  where libname = 'WORK'

    and memname = 'DATASET_1'

order by secq_1;

Quit;

proc sql;

create table name_2_secq as

  select name as name_2, input(substr(name,9,(length(name)-8)),2.) as secq_2

  from dictionary.columns

  where libname = 'WORK'

    and memname = 'DATASET_2'

order by secq_2;

quit;

proc sql;

select 'a.'||trim(left(a.name_1))||','||'b.'||trim(left(b.name_2)) into :name_strin separated by ','

from name_1_secq as a

inner join

name_2_secq as b

on a.secq_1 = b.secq_2;

quit;


proc sql;

create table joined as

select a.id,a.date, &name_strin.

from DATASET_1 as a

<your join>

DATASET_2 as b

<your joining condition>

quit;

View solution in original post


All Replies
Super Contributor
Posts: 282

Re: Alternate columns in a dataset

Hi,

If I've understood your requirements correctly, does the below code help? I have left in some %put statements to help make things clearer, they can be removed.

data dataset1;

  retain giveunit1

         giveunit2

         giveunit3

         giveunit4 0

  ;

run;

data dataset2;

  retain givecust1

         givecust2

         givecust3

         givecust4 0

  ;

run;

proc sql noprint;

  select name into: ds1vars separated by ' '

  from dictionary.columns

  where libname = 'WORK'

    and memname = 'DATASET1'

  ;

  select name into: ds2vars separated by ' '

  from dictionary.columns

  where libname = 'WORK'

    and memname = 'DATASET2'

  ;

quit;

%put ds1vars=&ds1vars;

%put ds2vars=&ds2vars;

data _null_;

  length dsvars $ 1000;

  ds1max=input(substr(reverse("&ds1vars"),1,1),8.);

  ds2max=input(substr(reverse("&ds2vars"),1,1),8.);

  maxvar=max(ds1max,ds2max);

  do i=1 to maxvar;

    dsvars=catx(' ',dsvars,scan("&ds1vars",i),scan("&ds2vars",i));

  end;

  call symput('dsvars',dsvars);

run;

%put dsvars=&dsvars;

data want;

  retain id

         date

         &dsvars 0

  ;

run;

Regards,

Amir.

Message was edited by: Amir Malik - format some code.

Super Contributor
Posts: 282

Re: Alternate columns in a dataset

Hi,

Further to my previous post, I would advise changing the two lines of code:

ds1max=input(substr(reverse("&ds1vars"),1,1),8.);

ds2max=input(substr(reverse("&ds2vars"),1,1),8.);

to:

ds1max=input(compress(scan("&ds1vars",-1),,'kd'),8.);

ds2max=input(compress(scan("&ds2vars",-1),,'kd'),8.);

so that if the number at the end of the variable name is greater than one digit big then it will be handled.

Regards,

Amir.

Super Contributor
Posts: 1,636

Re: Alternate columns in a dataset

data have;
input id $giveunit1-giveunit10 givecust1-givecust10;
cards;
aa 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
;

proc sql noprint;
  select name into : names separated by ' '
   from dictionary.columns
     where libname="WORK" and memname="HAVE" and upcase(substr(name,1,4))='GIVE'
       order by length((compress(name,,'kd'))),compress(name,,'kd'),(compress(name,,'ka')) desc;
quit;
%put &names;
data want;
retain id &names;
set have;
run;
proc print;run;

Respected Advisor
Posts: 4,646

Re: Alternate columns in a dataset

Or you can do it entirely with datasteps (thank you Linlin for the testing code) :

data have;
input id $ giveunit1-giveunit10 givecust1-givecust10;
cards;
aa 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
;


data _null_;
length cmd $1000;
set have;
array u{*} giveunit:;
array c{*} givecust:;
do i = 1 to min(dim(u), dim(c));
     do var = "unit", "cust";
          cmd = catx(" ", cmd, cats("give",var,i));
          end;
     end;
call symputx("retainList", cmd);
stop;
run;

data want;
retain id &retainList;
set have;
run;

PG

PG
Solution
‎06-19-2013 05:23 AM
Occasional Contributor
Posts: 18

Re: Alternate columns in a dataset

Hi Jeff-DOC,

Please try this code I am sure this will meet your requirement.

Please Drop your feedback and mark answer as correct if your purpose is solved.


proc sql;

create table name_1_secq as

  select name as name_1, input(substr(name,9,(length(name)-8)),2.) as secq_1

  from dictionary.columns

  where libname = 'WORK'

    and memname = 'DATASET_1'

order by secq_1;

Quit;

proc sql;

create table name_2_secq as

  select name as name_2, input(substr(name,9,(length(name)-8)),2.) as secq_2

  from dictionary.columns

  where libname = 'WORK'

    and memname = 'DATASET_2'

order by secq_2;

quit;

proc sql;

select 'a.'||trim(left(a.name_1))||','||'b.'||trim(left(b.name_2)) into :name_strin separated by ','

from name_1_secq as a

inner join

name_2_secq as b

on a.secq_1 = b.secq_2;

quit;


proc sql;

create table joined as

select a.id,a.date, &name_strin.

from DATASET_1 as a

<your join>

DATASET_2 as b

<your joining condition>

quit;

Contributor
Posts: 37

Re: Alternate columns in a dataset

Gaurang.

I am very much a SAS novice so I really appreciate all the education from you and everyone else here. I did end up using your solution and I got it to work. Thanks very much to you and everyone else!

Jeff

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 278 views
  • 10 likes
  • 5 in conversation