DATA Step, Macro, Functions and more

To identify words in catx column

Reply
Contributor
Posts: 22

To identify words in catx column

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.

 

 

Contributor
Posts: 22

Re: To identify words in catx column

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.

Respected Advisor
Posts: 4,173

Re: To identify words in catx column

[ Edited ]

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

Contributor
Posts: 22

Re: To identify words in catx column

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
FX
TG
GX
AB
CD

 

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;

 

 

header1|header2|
test1|test2
test3|test|

Fund Type|
FX|
TG|
GX|
AB|

Respected Advisor
Posts: 4,173

Re: To identify words in catx column

@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;
Ask a Question
Discussion stats
  • 4 replies
  • 109 views
  • 1 like
  • 2 in conversation