topic Re: Reading data in SAS Programming

Reading data

pdhokriya — Wed, 10 Feb 2021 17:41:06 GMT

Hi Reader,

Here is the data and code, facing issue while handelling semicolon and length , not getting correct output which is mentioned below in table.

Also I am not getting any error/warning in my program.

Here, if length LE 10 then i want to consider semicolon (like col1-1st obs) as it is else ignore semicolon (like col1-2nd obs).

Output required-

OLD	COL	COL1	COL2
abcd;xyzxybav;charged;	abcd;	xyzxybav;	charged;
ab;countrynew;zcc;	ab;	countrynew	zcc;
ab;charged;	ab;charged
ab;cd;dr;	ab;cd;dr;
ab	ab

data abc ;
old = "abcd;xyzxybav;charged;"; output;
old = "ab;countrynew;zcc;"; output;
old = "ab;charged;"; output;
old = "ab;cd;dr;"; output;
old = "ab"; output;
run;

%let w = 10 ;                                                                                             
     
data have  (drop = _:);
 set abc;                                                                                                                                                                                                                                               
 array c $ &w col col1-col2 ;
/*if old^="" and length(strip(old)) le &w then col=old; */
_i_ = 1 ;
do _x = 1 to countw (old, "; ") ;
c = catx ("; ", c, scan (old, _x, "; ")) ;
if length (c) <= &w and length (c) + length (scan (old, _x + 1)) > &w or _x = countw (old, "; ") then _i_ + 1 ;
end ;
run ;

Re: Reading data

PaigeMiller — Wed, 10 Feb 2021 16:08:32 GMT

Are you getting ERRORs or WARNINGs in the log? If so, please show us the log (paste it into the window that appears when you click on the </> icon)

Re: Reading data

ballardw — Wed, 10 Feb 2021 16:09:17 GMT

You might bother to actually state what is "wrong" in the output.

If the issue is the presence or lack of a semicolon then you really in a lot of detail need to describe why "contrynew" in col1 does not have semicolon at the end but "xyzxybav;" does.

Same with why "ab;charged;" and "ab;cd;dr;" are not split up but other values are.

Providing code that does not do what you want does not explain what the rules involved are.

Re: Reading data

pdhokriya — Wed, 10 Feb 2021 16:30:11 GMT

please see code.

Re: Reading data

pdhokriya — Wed, 10 Feb 2021 16:30:56 GMT

No error in pgrogram, just program need updation as per requirement.

Re: Reading data

PaigeMiller — Wed, 10 Feb 2021 16:37:17 GMT

@pdhokriya wrote:

please see code.

Your code has col1-col9 but your sample data does not have col1-col9 so we can't really use your code or your data as it is.

I agree with @ballardw , please explain what is wrong instead of assuming that we can figure out what is wrong.

Re: Reading data

ballardw — Wed, 10 Feb 2021 17:10:10 GMT

@pdhokriya wrote:

please see code.

I am not a mind reader.

If the code does not do what you want then there is no way the code can tell me what you want or expect.

Re: Reading data

pdhokriya — Wed, 10 Feb 2021 17:44:15 GMT

I have updated col1 -col2. please check the fyr.

also the code which i have given is not working correctly as i am not getting correct output (mentioned in table) so what shall i change in provided code so that will get correct ( table ) output.

Re: Reading data

pdhokriya — Wed, 10 Feb 2021 17:46:17 GMT

ok, please ignore my code, and please refer this-

data abc ;
old = "abcd;xyzxybav;charged;"; output;
old = "ab;countrynew;zcc;"; output;
old = "ab;charged;"; output;
old = "ab;cd;dr;"; output;
old = "ab"; output;
run;

and let me know how shall i get an output data.

Re: Reading data

Kurt_Bremser — Wed, 10 Feb 2021 17:58:28 GMT

This creates your intended result:

data abc ;
old = "abcd;xyzxybav;charged;"; output;
old = "ab;countrynew;zcc;"; output;
old = "ab;charged;"; output;
old = "ab;cd;dr;"; output;
old = "ab"; output;
run;

%let w = 10;

data want;
set abc;
length col col1 col2 $&w.;
if length (old) gt &w. + 1
then do;
  col = scan(old,1,";") !! ";";
  col1 = scan(old,2,";") !! ";";
  col2 = scan(old,3,";") !! ";";
end;
else col = old;
run;

Re: Reading data

Tom — Wed, 10 Feb 2021 17:59:59 GMT

You really seem to be having trouble explaining what you want. From the example data step you posted a PROC PRINT will produce this report.

Obs    old

 1     abcd;xyzxybav;charged;
 2     ab;countrynew;zcc;
 3     ab;charged;
 4     ab;cd;dr;
 5     ab

Now if you want to convert those strings into multiple values by splitting on the semi-colons then you could use something like this.

data tall;
  row+1;
  set abc ;
  length new $20;
  do item=1 by 1 until(new=' ');
    new=scan(old,item,';');
    if item=1 or new ne ' ' then output;
  end;
run;

Which will produce data like this:

Obs    row    old                       new           item

  1     1     abcd;xyzxybav;charged;    abcd            1
  2     1     abcd;xyzxybav;charged;    xyzxybav        2
  3     1     abcd;xyzxybav;charged;    charged         3
  4     2     ab;countrynew;zcc;        ab              1
  5     2     ab;countrynew;zcc;        countrynew      2
  6     2     ab;countrynew;zcc;        zcc             3
  7     3     ab;charged;               ab              1
  8     3     ab;charged;               charged         2
  9     4     ab;cd;dr;                 ab              1
 10     4     ab;cd;dr;                 cd              2
 11     4     ab;cd;dr;                 dr              3
 12     5     ab                        ab              1

Which you could transform back into one observation per original ROW by using PROC TRANSPOSE.

proc transpose data=tall out=want(drop=_name_) prefix=col;
  by row ;
  id item;
  var new ;
run;

Result

Obs    row    col1    col2          col3

 1      1     abcd    xyzxybav      charged
 2      2     ab      countrynew    zcc
 3      3     ab      charged
 4      4     ab      cd            dr
 5      5     ab

If this is NOT what you want then please describe in detail how what you want is different. And what are the rules that explain how to get what you want.

Re: Reading data

pdhokriya — Thu, 11 Feb 2021 04:37:34 GMT

Ya You understood correctly, but may be something is missing.
and Yes I can elaborate more on this , for example , if i take below mentioned data and &w = 200 then

data abc ;
old = "ASCORBIC ACID;CALCIUM CARBONATE;CALCIUM PHOSPHATE;CHROMIUM;CYANOCOBALAMIN;FOLIC ACID;MAGNESIUM CARBONATE;MAGNESIUM HYDROXIDE;MANGANESE;NICOTINIC ACID;PANTOTHENIC ACID;POTASSIUM BICARBONATE;POTASSIUM CARBONATE;POTASSIUM PHOSPHATE DIBASIC;PYRIDOXINE;QUERCETIN;RIBOFLAVIN;SODIUM BICARBONATE;SODIUM PHOSPHATE;THIAMINE;THIOCTIC ACID;ZINC ASCORBATE"; output;

old = "ASCORBIC ACID;BIOTIN;CALCIUM;CALCIUM PHOSPHATE;CHROMIUM;CUPRIC OXIDE;CYANOCOBALAMIN;FOLIC ACID;IODINE;IRON;MAGNESIUM OXIDE;MANGANESE;MOLYBDENUM;NICKEL;NICOTINAMIDE;PANTOTHENIC ACID;PHYTOMENADIONE;POTASSIUM;PYRIDOXINE;RETINOL;RIBOFLAVIN;SELENIUM;SODIUM METASILICATE;THIAMINE;TOCOPHEROL;VITAMIN D NOS;ZINC"; output;

old = "ab;countrynew;zcc;"; output;
old = "ab;charged;"; output;
old = "ab;cd;dr;"; output;
old = "ab"; output;
run;

output would be :

example : obs = 1

col = "ASCORBIC ACID;CALCIUM CARBONATE;CALCIUM PHOSPHATE;CHROMIUM;CYANOCOBALAMIN;FOLIC ACID;MAGNESIUM CARBONATE;MAGNESIUM HYDROXIDE;MANGANESE;NICOTINIC ACID;PANTOTHENIC ACID;POTASSIUM BICARBONATE;"
col1 = "POTASSIUM CARBONATE;POTASSIUM PHOSPHATE DIBASIC;PYRIDOXINE;QUERCETIN;RIBOFLAVIN;SODIUM BICARBONATE;SODIUM PHOSPHATE;THIAMINE;THIOCTIC ACID;ZINC ASCORBATE"

example : obs = 2

col= "ASCORBIC ACID;BIOTIN;CALCIUM;CALCIUM PHOSPHATE;CHROMIUM;CUPRIC OXIDE;CYANOCOBALAMIN;FOLIC ACID;IODINE;IRON;MAGNESIUM OXIDE;MANGANESE;MOLYBDENUM;NICKEL;NICOTINAMIDE;PANTOTHENIC ACID;PHYTOMENADIONE;"
col1= "POTASSIUM;PYRIDOXINE;RETINOL;RIBOFLAVIN;SELENIUM;SODIUM METASILICATE;THIAMINE;TOCOPHEROL;VITAMIN D NOS;ZINC"

Re: Reading data

Tom — Thu, 11 Feb 2021 05:06:29 GMT

You are still missing the description of what you are trying to do.

Let's make another guess. You have a really long string of words delimited by semi-colon and you want to break it into smaller strings, but without splitting individual words. So essentially you are doing word wrapping like a word processor would to flow a paragraph onto a page.

So if the original string has a maximum length of 1000 and you want the new strings to each have a maximum length of 200 then you will probably need between a maximum of 5 to 10 of them to hold all of the possible words. (What would happen if every word was exactly 100 characters long?)

data want ;
  set abc ;
  array col [10] $200 ;
  target=1;
  do source=1 to countw(old,';');
     if col[target]=' ' then col[target]=scan(old,source,';');
     else if 200 < length(catx(';',col[target],scan(old,source,';'))) then do;
       target=target+1;
       col[target]=scan(old,source,';');
     end;
     else col[target]=catx(';',col[target],scan(old,source,';'));
  end;
run;

Or more simply generate the TALL structure instead. You can always add in a PROC TRANSPOSE step to convert it back into one observation with multiple columns.

data tall ;
  row+1;
  set abc ;
  length col 8 new $200;
  col=1;
  do source=1 to countw(old,';');
    if source>1 and vlength(new) < length(catx(';',new,scan(old,source,';'))) then do;
       output;
       col=col+1;
       new=' ';
    end;
    new=catx(';',new,scan(old,source,';'));
  end;
  if col=1 or new ne ' ' then output;
  drop source old;
run;

proc transpose data=tall out=want(drop=_name_) prefix=col;
  by row;
  id col;
  var new;
run;

Re: Reading data

pdhokriya — Thu, 11 Feb 2021 08:28:23 GMT

Yes You are correct,

Just 1 correction needed i.e. we cant ignore semicolon at the end which is a part of raw data. i.e.

col: ASCORBIC ACID;CALCIUM CARBONATE;CALCIUM PHOSPHATE;CHROMIUM;CYANOCOBALAMIN;FOLIC ACID;MAGNESIUM CARBONATE;MAGNESIUM HYDROXIDE;MANGANESE;NICOTINIC ACID;PANTOTHENIC ACID;POTASSIUM BICARBONATE;

col: ASCORBIC ACID;BIOTIN;CALCIUM;CALCIUM PHOSPHATE;CHROMIUM;CUPRIC OXIDE;CYANOCOBALAMIN;FOLIC ACID;IODINE;IRON;MAGNESIUM OXIDE;MANGANESE;MOLYBDENUM;NICKEL;NICOTINAMIDE;PANTOTHENIC ACID;PHYTOMENADIONE;

Re: Reading data

pdhokriya — Mon, 15 Feb 2021 04:48:47 GMT

Raw data is impacting in this code :

data want ;
set abc ;
array col [10] $200 ;
target=1;
do source=1 to countw(old,';');
if col[target]=' ' then col[target]=scan(old,source,';');
else if 200 < length(catx(';',col[target],scan(old,source,';'))) then do;
target=target+1;
col[target]=scan(old,source,';');
end;
else col[target]=catx(';',col[target],scan(old,source,';'));
end;
run;

@Tom: Could you please help me with this.

Re: Reading data

Tom — Mon, 15 Feb 2021 05:41:07 GMT

Do you want to append the superfluous semi-colon to the end of every split value? Or just the final one?

Either way just use the CATS() function to add it back at the appropriate point in the logic.

Re: Reading data

pdhokriya — Tue, 16 Feb 2021 05:24:12 GMT

I do not want to additional semicolon.

This issue was, raw data has semicolon but in output did not have semicolon as pe raw data.

i.e. in col1 = data should be with semicolon as present in raw, but in final output did not present.

****************************************************************************************

data abc ;
old = "ASCORBIC ACID;CALCIUM CARBONATE;CALCIUM PHOSPHATE;CHROMIUM;CYANOCOBALAMIN;FOLIC ACID;MAGNESIUM CARBONATE;MAGNESIUM HYDROXIDE;MANGANESE;NICOTINIC ACID;PANTOTHENIC ACID;POTASSIUM BICARBONATE;POTASSIUM CARBONATE;POTASSIUM PHOSPHATE DIBASIC;PYRIDOXINE;QUERCETIN;RIBOFLAVIN;SODIUM BICARBONATE;SODIUM PHOSPHATE;THIAMINE;THIOCTIC ACID;ZINC ASCORBATE"; output;

old = "ASCORBIC ACID;BIOTIN;CALCIUM;CALCIUM PHOSPHATE;CHROMIUM;CUPRIC OXIDE;CYANOCOBALAMIN;FOLIC ACID;IODINE;IRON;MAGNESIUM OXIDE;MANGANESE;MOLYBDENUM;NICKEL;NICOTINAMIDE;PANTOTHENIC ACID;PHYTOMENADIONE;POTASSIUM;PYRIDOXINE;RETINOL;RIBOFLAVIN;SELENIUM;SODIUM METASILICATE;THIAMINE;TOCOPHEROL;VITAMIN D NOS;ZINC"; output;

old = "ab;countrynew;zcc;"; output;
old = "ab;charged;"; output;
old = "ab;cd;dr;"; output;
old = "ab"; output;
run;

data want ;
set abc ;
array col [10] $200 ;
target=1;
do source=1 to countw(old,';');
if col[target]=' ' then col[target]=scan(old,source,';');
else if 200 < length(catx(';',col[target],scan(old,source,';'))) then do;
target=target+1;
col[target]=scan(old,source,';');
end;
else col[target]=catx(';',col[target],scan(old,source,';'));
end;
run;

Current :ASCORBIC ACID;CALCIUM CARBONATE;CALCIUM PHOSPHATE;CHROMIUM;CYANOCOBALAMIN;FOLIC ACID;MAGNESIUM CARBONATE;MAGNESIUM HYDROXIDE;MANGANESE;NICOTINIC ACID;PANTOTHENIC ACID;POTASSIUM BICARBONATE

Required (as present in raw) : ASCORBIC ACID;CALCIUM CARBONATE;CALCIUM PHOSPHATE;CHROMIUM;CYANOCOBALAMIN;FOLIC ACID;MAGNESIUM CARBONATE;MAGNESIUM HYDROXIDE;MANGANESE;NICOTINIC ACID;PANTOTHENIC ACID;POTASSIUM BICARBONATE;

Re: Reading data

Kurt_Bremser — Tue, 16 Feb 2021 08:45:49 GMT

@pdhokriya wrote:
I do not want to additional semicolon.

Your initial post does have trailing semicolons.

Re: Reading data

pdhokriya — Tue, 16 Feb 2021 10:38:13 GMT

Yes, but if semicolon is at 201 position than not required . but if semicolon is placed below 200 (after splitting) than I will required in the output (as per raw).