topic Re: Dynamic Varible in the table in SAS Programming

Dynamic Varible in the table

JHE — Thu, 28 Jun 2018 20:20:58 GMT

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

Re: Dynamic Varible in the table

PaigeMiller — Thu, 28 Jun 2018 20:23:13 GMT

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

Re: Dynamic Varible in the table

Reeza — Thu, 28 Jun 2018 20:23:28 GMT

I don’t understand the issue you’re facing. Can you explain in more detail?

Re: Dynamic Varible in the table

JHE — Thu, 28 Jun 2018 20:28:22 GMT

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.

Re: Dynamic Varible in the table

JHE — Thu, 28 Jun 2018 20:34:39 GMT

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 ','.

Re: Dynamic Varible in the table

ballardw — Thu, 28 Jun 2018 20:37:24 GMT

@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.

Re: Dynamic Varible in the table

Reeza — Thu, 28 Jun 2018 20:40:43 GMT

@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-variables/m-p/474246

Re: Dynamic Varible in the table

JHE — Thu, 28 Jun 2018 20:59:48 GMT

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

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

Re: Dynamic Varible in the table

Reeza — Thu, 28 Jun 2018 21:06:20 GMT

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:);

Re: Dynamic Varible in the table

JHE — Thu, 28 Jun 2018 21:08:45 GMT

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)

Re: Dynamic Varible in the table

Reeza — Thu, 28 Jun 2018 21:10:25 GMT

Note the colon and try it and tell me if it doesn't work.
catx(', ', of TYPE:);

Re: Dynamic Varible in the table

Astounding — Thu, 28 Jun 2018 21:20:43 GMT

You could use a simple method:

data want;

set have;

by I;

length combo $ 1000;

if first.i then combo = variable;

else combo = catx(', ', combo, variable);

if last.i;

drop variable;

rename combo = variable;

run;

Re: Dynamic Varible in the table

PaigeMiller — Thu, 28 Jun 2018 21:40:42 GMT

@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.

Re: Dynamic Varible in the table

JHE — Fri, 29 Jun 2018 14:08:13 GMT

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

Result of running above , have Table b:

LOB	TYpe1	Type2	Type3	..TYPE..i
100	MHSC	MHO1	MHSA	….?

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 :

LOB	TYPE
100	MHSC,MHO1,MHSA,…….TYPEi….

would not working ....???

Re: Dynamic Varible in the table

JHE — Fri, 29 Jun 2018 14:26:32 GMT

using proc sql, because we need group by LOB,

result should be as this ...

LOB	TYPE
100	MHSC,MHO1,MHSA,…….TYPEi….
200	MHSC,MHO1,MHSA,…….TYPEi….
300	MNO1

Re: Dynamic Varible in the table

Reeza — Fri, 29 Jun 2018 15:02:20 GMT

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

Re: Dynamic Varible in the table

JHE — Fri, 29 Jun 2018 17:20:05 GMT

data C;

input LOB $ Code $;

cards;

0100 MHSC

0100 MHSC1

0500 MSHD

0500 HENH

0400 DEHG

;

proc sort data=C;

by lob;

run;

DATA OUTC;

SET C;

BY LOB;

LENGTH TYPE $100;

IF FIRST.LOB THEN TYPE=CODE;

ELSE TYPE =CATX(',',TYPE);

IF LAST.LOB;

DROP Code;

RUN;

What I should modify , in order to have result as this :

100	MHSC,MHSC1
400	DEHG
500	MSDD,HENH

Re: Dynamic Varible in the table

Astounding — Fri, 29 Jun 2018 17:24:30 GMT

Looks like I forgot a RETAIN statement. For your program, it would be:

retain type;

It can go anywhere in the DATA step.

Also, CATX needs an additional piece:

else type = catx(',' , type , code);

Re: Dynamic Varible in the table

JHE — Fri, 29 Jun 2018 17:27:50 GMT

This result:

0100	MHSC,MHSC1
0400	DEHG
0500	MSDD,HENH

Re: Dynamic Varible in the table

Reeza — Fri, 29 Jun 2018 17:30:22 GMT

This works

data C;
input LOB $ Code $;
cards;
0100 MHSC
0100 MHSC1
0500 MSHD
0500 HENH
0400 DEHG
;

proc sort data=C;
by lob;
run;

proc transpose data=c out=ct prefix=_code;
by lob;
var code;
run;

data want;
set ct;
combined = catx(", ", of _code:);
run;

proc print data=want;
run;

@JHE wrote:

data C;

input LOB $ Code $;

cards;

0100 MHSC

0100 MHSC1

0500 MSHD

0500 HENH

0400 DEHG

;

proc sort data=C;

by lob;

run;

　

DATA OUTC;

SET C;

BY LOB;

LENGTH TYPE $100;

IF FIRST.LOB THEN TYPE=CODE;

ELSE TYPE =CATX(',',TYPE);

IF LAST.LOB;

DROP Code;

RUN;

　

What I should modify , in order to have result as this :

100

MHSC,MHSC1

400

DEHG

500

MSDD,HENH