topic Re: To identify words in catx column in SAS Programming

To identify words in catx column

Hhh111 — Tue, 19 Sep 2017 09:24:50 GMT

Hi,

There is one column in my dataset that have concatenated value. My mission here is to scan what items name that been separated by the comma value.

For example:

product

FA;TG

GX;AB;CD

I want to find any products existed in the dataset and display at the bottom of the textfile. How to remove the ; and delete duplicate to get unique value of that column.

footer:

Product|Product Info

FA|Financial Amount

TG|TQSJJ

AB|Adiasjjw

Please help to give some ideas on the solution. Thanks.

Re: To identify words in catx column

Hhh111 — Tue, 19 Sep 2017 10:07:55 GMT

actually i manage to find the solution. I will be using below coding

data want;

set test;

array parsed_vars(6)$10 new_var1-new_var6;

i=1;

do while(scan(product, i, ";") ne "");

parsed_vars(i) =scan(product, i, ";");

i+1;

end;

run;

proc print data=want; run;

data _null_;

set want;

length prodname $5;

array temp{6} new_var1 new_var2 new_var3 new_var4 new_var5 new_var6;

do prod=1 to 6;

prodname = temp{prod};

output;

end;

keep prodname;

proc sort nodupkey; by fundname;

run;

Now, i already have unique prodname. The only unanswered solution is to call that variable and putting at the footer of my delimeted textfile. Any idea on that? or maybe anyone would like to comment the above syntax. Thanks.

Re: To identify words in catx column

Patrick — Tue, 19 Sep 2017 12:04:28 GMT

@Hhh111

You kind-of make a "mess" between textfile and SAS table. Also in your example the HAVE strings don't match up with your desired result.

What would help us to provide you with actually working and tested code:

Attach a sample HAVE text file (only a few records and in a structure as simple as possible), attach a sample WANT text file, explain us exactly the logic required to get from HAVE to WANT.

Re: To identify words in catx column

Hhh111 — Wed, 20 Sep 2017 01:46:54 GMT

Sorry for the confusion.

HAVE	WANT
FA;TG	FA
	TG
GX;AB;CD	GX
	AB
	CD

I want to convert the 'Have' items into another new table (Want), ignoring the ;.

That's why im using below code

data want;

set have;

array parsed_vars(6)$10 new_var1-new_var6;

i=1;

do while(scan(result, i, ";") ne "");

parsed_vars(i) =scan(result, i, ";");

i+1;

end;

run;

proc print data=want; run;

However, the above code will produce more column as below

New_Var1	New_Var2	New_Var3	New_Var4	New_Var5	New_Var6
FA	TG	.	.	.	.
GX	AB	CD	.	.	.

To compile the value into one field, I'm using transpose:

data final;

set want;

length fundname $5;

array temp{6} new_var1 new_var2 new_var3 new_var4 new_var5 new_var6;

do fund=1 to 6;

fundname = temp{fund};

output;

end;

keep fundname;

proc sort nodupkey; by fundname;

run;

FUNDNAME

For the last part, I have to output the main dataset and still need to call fundname for the summary part at the footer. Here I have no idea how to call the variables.

DATA last;

SET main_dataset;

FILE "path\monthly..txt" DLM='|' lrecl=257;

IF _N_ = 1 THEN

PUT @1 'header|header|';

PUT @1 variable

variable

;

IF _N_=J THEN DO;

PUT @1 'FUND TYPE|FUND DESCRIPTION';

END;

run;

Fund Type|
FX|
TG|
GX|
AB|

Re: To identify words in catx column

Patrick — Wed, 20 Sep 2017 09:25:40 GMT

@Hhh111

If I understand your problem right then something like below should get you over the line.

%let want_txt_file=c:\temp\want.txt;

data have;
 input in_str:$20.;
 datalines4;
FA;TG
GX;AB;CD;TG
;;;;
run;

filename outtxt "&want_txt_file";

data _null_;

  /*** write main data set to text file ***/
  file outtxt;
  set have end=last;
  if _n_=1 then put 'Header 1: In_Str';
  put in_str;


  /*** collect unique fund_types ***/
  if _n_=1 then
    do;
      length Fund_Type $5.;
      dcl hash h1(ordered:'y');
      h1.defineKey('Fund_Type');
      h1.defineData('Fund_Type');
      h1.defineDone();
    end;

  do _i=1 by 1;
    Fund_Type=scan(in_str,_i,';');
    if missing(Fund_Type) then leave;
    h1.ref();
  end;

  /* write unique fund_types to output table */
  if last then
    do;
      h1.output(dataset:'Fund_Types_Deduped');
    end;
run;

data _null_;
  /*** write unique Fund_Types and append to text file ***/
  file outtxt mod;
  set Fund_Types_Deduped;
  if _n_=1 then put 'Fund Type';
  put Fund_Type;
run;