topic Re: How do we create column from string where '\' is used to separate the sub string in SAS ? in SAS Programming

How do we create column from string where '\' is used to separate the sub string in SAS ?

vinayakraja — Tue, 29 Mar 2016 00:37:37 GMT

I have been trying to create columns in sas based on substring. I need to create a coloumn of each sub string stored in a column seprated by '\'. For example:-

prexa\Medical Information\Medical Letters 02 is a single string stored in one column. i would like to have three columns col1: prexa col2 :Medical Information and Col3 : Medical. I am new to sas and have tried but unable to proceed.

this column has numeric values with no sub string as well.

Re: How do we create column from string where '\' is used to separate the sub string in SAS ?

Astounding — Tue, 29 Mar 2016 00:42:53 GMT

I'm not sure this handles everything you see in your data, but the right tool to split up the text is the SCAN function:

long_string = "prexa\Medical Information\Medical";

col1 = scan(long_string, 1, '\');

col2 = scan(long_string, 2, '\');

col3 = scan(long_string, 3, '\');

Re: How do we create column from string where '\' is used to separate the sub string in SAS ?

vinayakraja — Tue, 29 Mar 2016 00:49:40 GMT

Thanks a lot for your reply. I have used scan function but the data just not contain 3 substring but multiple substring( shared just snapshot of data in image) . I am dealing with 100,000+ observations so I need to have something that can be dynamic. I have been thinking of using do while loop while putting substring function having '\' something similar. Can you please suggest ? Thanks again.

Re: How do we create column from string where '\' is used to separate the sub string in SAS ?

ChrisNZ — Tue, 29 Mar 2016 01:41:48 GMT

And to populate the numeric variable you can add this:

  NUM=input(LONG_STRING, ?? dollar32.);
  if NUM=. then do;
    COL1 = scan(LONG_STRING, 1, '\');
    COL2 = scan(LONG_STRING, 2, '\');
    COL3 = scan(LONG_STRING, 3, '\');
  end;

Re: How do we create column from string where '\' is used to separate the sub string in SAS ?

vinayakraja — Tue, 29 Mar 2016 00:58:44 GMT

Re: How do we create column from string where '\' is used to separate the sub string in SAS ?

ChrisNZ — Tue, 29 Mar 2016 01:42:49 GMT

Like this?

  array COL [99] $32;               
  NUM=input(LONG_STRING, ?? dollar32.);       
  if NUM=. then do I=1 to 99 until (COL[I]='');
    COL[I] = scan(LONG_STRING, I, '\');      
  end;

Edited: I had the informat name wrong !

Re: How do we create column from string where '\' is used to separate the sub string in SAS ?

vinayakraja — Tue, 29 Mar 2016 01:14:12 GMT

thanks a lot. can you please help me understand. NUM=input(LONG_STRING, ?? $32.); I understand input function but can you please explain the role of "??". i have never used it.

Re: How do we create column from string where '\' is used to separate the sub string in SAS ?

Astounding — Tue, 29 Mar 2016 01:28:10 GMT

The number of observations isn't so important. It's the variables that matter.

You might consider skipping functions entirely and just using "\" as a delimiter when reading in the variables:

infile rawdata dlm="\" dsd;

input col1 $ col2 $ col3;

But you have to figure out where all the variables are. It won't do to figure out 3 at a time.

Re: How do we create column from string where '\' is used to separate the sub string in SAS ?

ChrisNZ — Tue, 29 Mar 2016 01:29:51 GMT

Look up the input function documentation, it's all there. 🙂

Re: How do we create column from string where '\' is used to separate the sub string in SAS ?

vinayakraja — Tue, 29 Mar 2016 01:35:42 GMT

thanks a lot. i will look into this. really Appericiate your help.

Re: How do we create column from string where '\' is used to separate the sub string in SAS ?

vinayakraja — Tue, 29 Mar 2016 01:37:02 GMT

its not raw data file but a coloum with a string. and its substring are seprated by '/'. thanks again for all your help.

Re: How do we create column from string where '\' is used to separate the sub string in SAS ?

Astounding — Tue, 29 Mar 2016 02:18:45 GMT

So given that you have a SAS data set with a single variable in it, what do you want the outcome to be at the end of all the processing?

Re: How do we create column from string where '\' is used to separate the sub string in SAS ?

RW9 — Tue, 29 Mar 2016 08:55:49 GMT

Hi,

Well the first question you should be asking yourself is what is this data, how did I arrive with it. Now I do not know what the numbers are but the other part looks like some sort of directory listing. Is this data you have created? If so I would fix the code which generates this data, if it comes from a vendor then update your agreement so the data is in a useable format. Without knowledge of what the data is or how you came by it something like the below code might help, it creates a series of character variales for each split of the long string - note I do not do any conversions to number or anything else, just split the string up. The reason is I do not know this data, nor what its used for. 99% of any programming task is to understand your data and arrange that in a manner which is conducive to programming, the example data you provided looks like at least two different data items appended to each other - this is generally not good. Also, please post test data in the form of a datastep as text in your posts, its not great to have to type all that in to test code:

data have;
  infile datalines dlm="¬";
  length long_string $2000;
  input long_string $;
datalines;
6
7
3
4
P Toolbox\Customer program\peer to peer
abc\ert\popt\abcdedgeg\defty
;
run;

proc sql noprint;
  select  max(countw(LONG_STRING,"\"))
  into    :NUM_ARRAY
  from    HAVE;
quit;

data want;
  set have;
  array col{&num_array.} $100;
  do i=1 to countw(long_string,"\");
    col{i}=scan(long_string,i,"\");
  end;
run;

Re: How do we create column from string where '\' is used to separate the sub string in SAS ?

ballardw — Tue, 29 Mar 2016 14:31:23 GMT

SHOW what your expected output should look like.

Re: How do we create column from string where '\' is used to separate the sub string in SAS ?

vinayakraja — Tue, 29 Mar 2016 22:13:35 GMT

The variable has long string and has to be divided into substrings and divided into columns of substrings. At the end we need to have total for each observation which has total for each populated coloumn. I hope i cleared it. Thanks for all the help.

Re: How do we create column from string where '\' is used to separate the sub string in SAS ?

vinayakraja — Tue, 29 Mar 2016 22:24:22 GMT

Thanks for the solution.

Re: How do we create column from string where '\' is used to separate the sub string in SAS ?

vinayakraja — Tue, 29 Mar 2016 22:24:56 GMT

Thanks for the solution.