BookmarkSubscribeRSS Feed
JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

I have table A:

ivariable
1x1
1x2
1x3
1x4
1x5
1xi

 

How to have table B as :

ivariable
1x1,x2,x3,x4,x5..xi

 

the problem is , 'xi' , 'i'  isdynamic value, from Proc Transpose Prefix option.

 

I need to know  HOW  from table A to B, and handle xi dynamically.

 

Thank you

 

22 REPLIES 22
PaigeMiller
Diamond | Level 26

PROC TRANSPOSE is dynamic, there is no need for you to program the dynamic aspect of this operation.

--
Paige Miller
JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

PROC Transpose the dynamic aspect of this operation result is multiple rows,

I need concatenate x1,x2,x3,x4..xi   to one record - one row  separated by ','.

PaigeMiller
Diamond | Level 26

@JHE wrote:

I need concatenate x1,x2,x3,x4..xi   to one record - one row  separated by ','.


It's hard to imagine any useful reason to create this variable as a concatenation of the variable names.

--
Paige Miller
Reeza
Super User
I don’t understand the issue you’re facing. Can you explain in more detail?
JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

When I have table A as PROC Transpose result.

 

Next step need to have table B .

 

How to have Table B from Table A

 

After Proc Transpose I have Table A, how many rows are unknown . ...'xi'. But customer need to concatenate xi rows to one recode--- one row.

Reeza
Super User

@JHE wrote:

When I have table A as PROC Transpose result.

 

Next step need to have table B .

 

How to have Table B from Table A

 

After Proc Transpose I have Table A, how many rows are unknown . ...'xi'. But customer need to concatenate xi rows to one recode--- one row.


You don't need to know how many. You can use a variable short cut with the PREFIX approach to concatenate all together.

 

catx(", ", of my_prefix: );

Using a naming convention and your dynamic approach will work. Or don't transpose and do it line by line and output at the last using LAST. Like in this question: https://communities.sas.com/t5/Base-SAS-Programming/vertical-concatenation-based-on-grouping-variabl...

 

 

JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

oh, I see,      CATX(',', of my_prefix: ) , I have problem to get  'of my_prefix: ' ,

 

PROC TRANSPOSE DATA=FACETS_PROVIDERa PREFIX=TYPE OUT=FACETS_PROVIDERb (drop=_name_ _label_);

by lob service_provider_id;

var type_of_service;

RUN;

 

From this code, I could have Type1, type2......typen, 'n' would be my problem, do not know how to get it.

 

Affect my next step CATX(',',type1, type2...typen)

 

 

 

 

 

 

Reeza
Super User
Note the colon and try it and tell me if it doesn't work.
catx(', ', of TYPE:);
JHE
Obsidian | Level 7 JHE
Obsidian | Level 7
ix1x2x3x4x5xi

 

PROC Transpose result, since xi 'i' is unknown, when I need to use

CATX(',',x1,x2,x3....xi), but could not decide 'i'..

Reeza
Super User

6 easy ways to specify a list of variables in SAS

https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

 

X_COMBO = catx(", ", of X:);

 

JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

PROC TRANSPOSE DATA=a PREFIX=TYPE OUT=b (drop=_name_ _label_);
by lob;
var typ;
RUN;

 

Result of running above , have Table b:

LOBTYpe1Type2Type3..TYPE..i
100MHSCMHO1MHSA….?

 

Note: Type..i would be unknown, dynamic.

 

Want to have a new table C: I am a SQL person ...

PROC SQL;
 CREATE TABLE c as
  SELECT LOB,
             CATX(',',of TYPE:) AS Service_Codes,
  FROM B;
QUIT;

 

I want result :

LOBTYPE
100MHSC,MHO1,MHSA,…….TYPEi….

 

would not working ....???

 

Reeza
Super User

SQL doesn't support variable lists so you'll need to know the i value or use a data step instead.

ballardw
Super User

@JHE wrote:

I have table A:

i variable
1 x1
1 x2
1 x3
1 x4
1 x5
1 xi

 

How to have table B as :

i variable
1 x1,x2,x3,x4,x5..xi

 

the problem is , 'xi' , 'i'  isdynamic value, from Proc Transpose Prefix option.

 

I need to know  HOW  from table A to B, and handle xi dynamically.

 

Thank you

 


If I understand what you want you will likely have an issue with assigning a length to the new variable that contains all of those values.

If the x1 , x2 etc are numeric you also need to tell us what precision you need for each value as the only way to accomplish anything close the that would require turning all of the values into character values so that you could combine them into a single string.

 

Perhaps you can share what you will actually do with that resulting data set as perhaps this step isn't needed at all.

JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

using proc sql, because we need group by LOB,

 

result should be as this ...

 

 

LOBTYPE
100MHSC,MHO1,MHSA,…….TYPEi….
200MHSC,MHO1,MHSA,…….TYPEi….
300MNO1

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 22 replies
  • 1729 views
  • 5 likes
  • 5 in conversation