BookmarkSubscribeRSS Feed
Hhh111
Calcite | Level 5

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.

 

 

4 REPLIES 4
Hhh111
Calcite | Level 5

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.

Patrick
Opal | Level 21

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

Hhh111
Calcite | Level 5

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|

Patrick
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 787 views
  • 1 like
  • 2 in conversation