Concatenating string and numeric values

[Ihsan-Mahdi]

Hello,

I am working on a data set flattened using proc transpose. The resulting set has 34 columns or two variables. I would like to concatenate these columns into two (one for each variable). I used the CATX function in this manner:

data want;
length Medication $200 Response $200;
set have;
Medication=catx(",", Medication1-Medication34);
Response=catx(",", Response1-Response34);
drop Medication1-Medication34  Response1-Response34;
run;

but it didn't work producing to variables with all blanks. No error and no warning.

Any suggestions/tips on where I went wrong? Also, if I were to do the same thing with numeric variables, which concatenation function would be the best to use?

Thank you!

[Ihsan-Mahdi]

Sorry, related to my question this is an example of what I have:

ID	Medication1	Medication2	Medication…	Medication34	Response1	Response2	Response….	Response34
1	X	Y	Z	W	E
2	X	Y	H		F
3	A	B	C		G	H
4					M	N	O	R
5					M	K	L	P

and this is what I want:

ID	Medication	Response
1	X, Y, Z, W	E
2	Y, H	F
3	A, B, C	G, H
4		M, N, O, R
5		M, K, L, P

[Kurt_Bremser · Accepted solution]

Medication1-Medication34

is a calculation, unless you use

of Medication1-Medication34

to indicate to the data step compiler that this is in fact a variable list. But I don't know if this will work with CATX, and I can't test it here on my tablet.

[Ihsan-Mahdi]

WOW! This actually worked, thank you so much 🙂

Would it work on numeric values?

[Kurt_Bremser]

To use numeric data in character functions, you must make sure that the conversion happens in an orderly manner. Use the PUT function and proper formats to avoid unwanted blanks etc.

[ballardw]

Did't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "<>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "<>" icon or attached as text to show exactly what you have and that we can test code against.

Really, the LOG told you pretty much exactly what happens. Here is a very brief example of making a small data set and the using a couple of ways to do what you want and what you did incorrectly:

data example;
  input x1 $ x2 $ x3 $;
datalines;
a b c
p d .
;

data want;
  set example;
  result1 = catx(',', of x:);
  result2 = catx(',', of x1-x3);
  result3 = catx(',',x1-x3);
run;

And here's the Invalid data message that you got from your approach, Result3 above:

80   data want;
81     set example;
82     result1 = catx(',', of x:);
83     result2 = catx(',', of x1-x3);
84     result3 = catx(',',x1-x3);
85   run;

NOTE: Character values have been converted to numeric values at the places given by:
      (Line):(Column).
      84:22   84:25
NOTE: Invalid numeric data, x1='a' , at line 84 column 22.
NOTE: Invalid numeric data, x3='c' , at line 84 column 25.
x1=a x2=b x3=c result1=a,b,c result2=a,b,c result3=. _ERROR_=1 _N_=1
NOTE: Invalid numeric data, x1='p' , at line 84 column 22.
x1=p x2=d x3=  result1=p,d result2=p,d result3=. _ERROR_=1 _N_=2
NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      2 at 84:24

Large economy sized hints from the Log: First is the character converted to numeric note on the line for Result3.

Then the "invalid numeric" notes for the character values and that only two of them show the conversion. Lastly the "result of performing and operation on missing values" .