-1

I have multiple datasets with different columns. Let us suppose dataset 1 has col1,col2,col3 whereas datsets 2 as col1 and col2 only.

Now I want to combine all these columns with a "," (comma).I have created a macro that will combine all the columns(col1,col2,col3) with a comma. But if there are less number of columns in another dataset there will be extra comma that I do not want.

For example if I write a code total=catx(col1,col2,col3) the result will be 1,2,

All i want is my result to be 1,2 with no extra commas

Kindly suggest me how to do this using a do loop.

Code

%macro xyz (data, data_1);
   proc sort data =a.&data.;
   by Type;
   where Type ne " ";
   run;

proc transpose data=a.&data. out=a.&data1.;
var string;
run;

data a.&data1.;
set a.&data1.;
total=catx(",",col1,col2,col3);
run; 
%mend;
%xyz(gold,gold1);
%xyz(silver,silver1);

so if there are three variables in gol1 but 2 in silver1 on concatenating the total will give me a extra comma in the total variable which I do not require.I know because I have given a extra column(col3) in the catx statement so thats y it will give me extra comma but as I said different datasets different number of variables

Swati Kanchan
  • 99
  • 2
  • 15
  • No, CATX accounts for missing values and even missing variables though you will get a message in the log. – Reeza Feb 13 '16 at 20:08
  • @Reeza, So how do i remove that extra comma if there are less variables than the columns mentioned in my catx statement.I basically want to use a macro to do this for 100 datasets. Each dataset has different number of variables and if there are less number of variables i do not want a extra comma in my final concatenated variable – Swati Kanchan Feb 13 '16 at 20:12
  • CatX doesn't include them in my tests. Post your code if you're having issues. – Reeza Feb 13 '16 at 20:13
  • @Reeza..codde has been added. Thank you – Swati Kanchan Feb 13 '16 at 20:25
  • Dynamically calculate the number of variables then. I'll post the code. – Reeza Feb 13 '16 at 20:27
  • @Reeza.Thank you so much for posting the code and helping me out.Thank you again – Swati Kanchan Feb 13 '16 at 20:35

2 Answers2

0

What happens if you do this:

data a.&data1.;
set a.&data1.;
array col(3) col1-col3;
total=catx(",", of col(*));
run;

The answer depends on how you list the variables. If you use OF it accounts for the missing values, if you use the explicit list method, it doesn't.

Reeza
  • 20,510
  • 4
  • 21
  • 38
0

Given your example I would use a name prefix list.

total=catx(',',OF COL:);

SAS Variable Lists

data _null_
  • 8,534
  • 12
  • 14