topic Re: Split one column values to two different columns in New SAS User

Split one column values to two different columns

KranthiK_J — Sun, 05 Mar 2023 15:20:16 GMT

Hi,

I have a column called features which has 9 rows. Now i have to split this 9 rows into 2 new columns equally called as feature_1 and feature_2.

Lets say feature_1 should have 5 rows and feature_2 should have 4 rows.

Sample data i have:

B_DATE	MOBILE_NUM	FEATURES
1/01/22	204-390-1234	Unlimited Local Talking
1/01/22	204-390-1234	10GB Data Smartphone
1/01/22	204-390-1234	Message Centre
1/01/22	204-390-1234	Call Display
1/01/22	204-390-1234	Unlimited Canadian & Can-US LD
1/01/22	204-390-1234	BCE 5G Access - Corp
1/01/22	204-390-1234	Daily Roam
1/01/22	204-390-1234	Call Forwarding
1/01/22	204-390-1234	Messaging Pack Unlimited

Sample Output Expected:

B_DATE	MOBILE_NUM	  Feature1	                    Feature2
1/01/22	204-390-4356	Unlimited Local Talking	
1/01/22	204-390-4356	10GB Data Smartphone	       BCE 5G Access - Corp
1/01/22	204-390-4356	Message Centre	               Daily Roam
1/01/22	204-390-4356	Call Display	               Call Forwarding
1/01/22	204-390-4356	Unlimited Canadian & Can-US LD	Messaging Pack Unlimited

Any suggestions would be appreciated!

Thanks.

Re: Split one column values to two different columns

Quentin — Sun, 05 Mar 2023 16:07:18 GMT

Why do you want to split the data in this way? What is the logic used to determine where to split the data?

In the real problem, do you have more dates/mobile numbers in your data? If so, it would be helpful to see test data that shows more data.

Re: Split one column values to two different columns

Tom — Sun, 05 Mar 2023 16:42:45 GMT

You don't state WHY this would be a useful thing to do. What is the reason that you want make the data more complicated and less useful?

Usually this is part of some reporting process where you want to get as much data on the page as possible.

So here is a method using DO loop and ARRAY.

data want;
   do index=1 to dim(_features) until(last.mobile_num);
      set have;
      by r_date mobile_num;
      array _features $50 feature_1 feature_2 ;
      _features[index]=features;
   end;
   drop features;
run;

Re: Split one column values to two different columns

KranthiK_J — Sun, 05 Mar 2023 22:41:00 GMT

I want to split the data into two columns as it's the requirement for reporting. However, it does have multiple dates with multiple mobile numbers

Re: Split one column values to two different columns

KranthiK_J — Mon, 06 Mar 2023 14:06:19 GMT

Tried this, but getting the below error

ERROR: The DIM, LBOUND, and HBOUND functions require an array name for the first argument.

Re: Split one column values to two different columns

Tom — Mon, 06 Mar 2023 14:28:49 GMT

@KranthiK_J wrote:

Tried this, but getting the below error

ERROR: The DIM, LBOUND, and HBOUND functions require an array name for the first argument.

The data step editor is not smart enough to understand that the DIM() function takes an array reference, so it defines _FEATURES as a regular variable.

You could just hard code the upper bound of the DO loop.

You could move the placement of the ARRAY statement (but that would create the new variables at the start of the dataset instead of the end).

Re: Split one column values to two different columns

Quentin — Mon, 06 Mar 2023 16:07:46 GMT

@KranthiK_J wrote:

I want to split the data into two columns as it's the requirement for reporting. However, it does have multiple dates with multiple mobile numbers

Are you trying to generate a report or trying to generate a dataset?

If it is a report, it would be better describe the report you want. It's probably possible to generate a report with data in tow columns without actually transforming your data into that format.

Also, can you please expand your example to show how you would want to handle multiple dates / multiple mobile numbers? Is there always the same group of 9 features for each date-mobile number, or can it vary?

Re: Split one column values to two different columns

KranthiK_J — Mon, 06 Mar 2023 16:09:18 GMT

data want;
   array _charge_descr_en $60 feature_1 feature_2;
   do index=1 to dim(_charge_descr_en) until(last.subscriber_num);
      set EXTRACT_FEATURES;
      by billed_date subscriber_num;
      array _charge_descr $50 feature_1 feature_2 ;
      _charge_descr[index]=features;
   end;
   drop features;
run;

I used this one here, however, i am not getting any errors....and also i am not getting any data in feature_1 and feature_2....i am getting nulls in those two columns alone.

Billed_Date            Subscriber_num        Feature_1              Feature_2

17DEC2022:00:00:00.000	4038353444                                                                                  		                                                  	                                                  
17DEC2022:00:00:00.000	4038353444                                                                                  	                                                  	                                                  
17DEC2022:00:00:00.000	4038353444                                                                                 		                                                  	                                                  
17DEC2022:00:00:00.000	4038353444                                                                                  		                                                  	                                                  
17DEC2022:00:00:00.000	4038353444                                                                                  		                                                  	                                                  
17DEC2022:00:00:00.000	4038353444                                                                                  		                                                  	                                                  
17DEC2022:00:00:00.000	4038353444

Re: Split one column values to two different columns

KranthiK_J — Mon, 06 Mar 2023 16:11:38 GMT

Yeah i am trying to generate a report...and i am storing these value in the dataset for future reference as well(history load).

And yes, it always varies....for each mobile number and B_date the features count can vary. It is so dynamic.

Re: Split one column values to two different columns

Tom — Mon, 06 Mar 2023 16:25:12 GMT

Make sure to use the same name for the array everywhere.

array _charge_descr_en
dim(_charge_descr_en) 
array _charge_descr
_charge_descr[index]

Try:

data want;
   do index=1 to 2 until(last.subscriber_num);
      set EXTRACT_FEATURES;
      by billed_date subscriber_num;
      array _charge_descr $60 feature_1 feature_2 ;
      _charge_descr[index]=features;
   end;
   drop features;
run;

Re: Split one column values to two different columns

KranthiK_J — Mon, 06 Mar 2023 17:34:19 GMT

Tried the solution but still having the same result( feature_1 and feature_2 are nulls as output

Re: Split one column values to two different columns

KranthiK_J — Mon, 06 Mar 2023 17:50:46 GMT

It is working now, it was my mistake to have a wrong variable.. Thank you so much. Its a life saver.