DATA Step, Macro, Functions and more

create a dataset from a separated list

Reply
Regular Contributor
Posts: 229

create a dataset from a separated list

%let Ctx = "LotId;P110123; DuploWaferId;D02; Operation; 1000-OxideDepostion!1!0";

In this example this are always pairs. I want a table with columns LotId DuploWaferId Operation with the correct values.

I tried already a few things, but what is the best way?

%let CtxFormat="CtxName;CtxValue";
%let Ctx = "LotId;P110123; DuploWaferId;D02; Operation; 1000-OxideDepostion!1!0";

data _null_;
aantal = %sysfunc(countw(&CtxFormat,';'));
call symputx('aantal',aantal);
run;
%put &aantal;

data _null_;
aantal = %sysfunc(countw(&Ctx,';'));
call symputx('aantalvar',aantal);
run;
%put &aantalvar;

data format;
    array format{&aantal} $20.;
i = 1;
    do while (scan(&CtxFormat,i,';') NE '');
format{i} = scan(&CtxFormat,i,';');
i = i +1;
end;
run;

data colnames(keep=vname);
  set format (obs=1) ;
  array n{*} _NUMERIC_ ;
  array c{*} _CHARACTER_ ;
  do i = 1 to dim(n) ;
    vname = vname(n{i}) ;
    output ;
  end ;
  do i = 1 to dim(c) ;
    vname = vname(c{i}) ;
output;
  end ;
run ;

proc sql;
select vname into:cols separated by ' ' from colnames where vname ne 'i';
quit;
%put &cols;

proc transpose data=format out=formattransp(keep=format1) prefix=format;
var &cols;
by i;
run;

data _null_;
set formattransp;
if upcase(format1) = "CTXNAME" then call symputx('PlaceName',_N_);
if upcase(format1) = "CTXVALUE" then call symputx('PlaceValue',_N_);
run;
%put &PlaceName &PlaceValue;


/* read values */
/* aantal = number of blocks per variable */
/* aantalvar = number of variables in ctx */
/* PlaceName = Place of the name of the variable */
/* PlaceValue = Place of the value of the variable */

data temp_variables;
    array format{&aantalvar} $50.;
i = 1;
    do while (scan(&Ctx,i,';') NE '');
format{i} = compress(scan(&Ctx,i,';'));
i = i +1;
end;
run;

data colnames(keep=vname);
  set temp_variables (obs=1) ;
  array n{*} _NUMERIC_ ;
  array c{*} _CHARACTER_ ;
  do i = 1 to dim(n) ;
    vname = vname(n{i}) ;
    output ;
  end ;
  do i = 1 to dim(c) ;
    vname = vname(c{i}) ;
output;
  end ;
run ;

proc sql;
select vname into:cols separated by ' ' from colnames where vname ne 'i';
quit;
%put &cols;

proc transpose data=temp_variables out=temp_variables_transp(keep=format1) prefix=format;
var &cols;
by i;
run;

data column_names(drop=teller) column_values(drop=teller);
set temp_variables_transp;
retain teller;
if _N_ = 1 then teller = 1;

if teller = &PlaceName then output column_names;
if teller = &PlaceValue then output column_values;

if mod(_N_,&aantal) = 0 then do;
  teller = 0;
end;
teller = teller + 1;
run;

proc sql;
select format1 into:columns separated by ' ' from column_names;
quit;
%put &columns;

Super Contributor
Posts: 264

Re: create a dataset from a separated list

Can you post the dataset you want to create? If i understood your request, you need something like this:

LotIdDuploWaferIdOperation
P110123D021000-OxideDepostion!1!0

Maybe something like this:

data _null_;

    Ctx = &g_Ctx;

    call execute('data work.Bill;');

    do i = 1 to (countc(Ctx, ';') + 1) by +2;

        Left = strip(scan(Ctx, i, ';'));

        Right = strip(scan(Ctx, i+1, ';'));

        call execute(trim(Left) !! ' = "' !! trim(Right) !! '";');

    end;

    call execute('run;');

run;

In this example this are always pairs

And what should happen, if this condition is not fulfilled.

Regular Contributor
Posts: 229

create a dataset from a separated list

hi, well I always need the Pair ctxName ctxValue, as you can see in my code i got this solved.

Also i got the table i wanted (and yes it is the same as yours)

/* create a dataset for the edoc */

data column_names(drop=teller) column_values(drop=teller);

set temp_variables_transp;

retain teller;

if _N_ = 1 then teller = 1;

if teller = &PlaceName then output column_names;

if teller = &PlaceValue then output column_values;

if mod(_N_,&aantal) = 0 then do;

       teller = 0;

end;

teller = teller + 1;

run;

proc sql; select format1 into:edoc_cols separated by '#' from column_names; quit;

proc sql; select format1 into:edoc_values separated by '#' from column_values; quit;

%put &edoc_values;

%put &edoc_cols;

data edoc_temp_2;

set edoc_temp;

i = 1;

do while (scan("&edoc_values",i,'#') NE '');

      temp_var = compress(scan("&edoc_cols",i,'#'));

      temp_val = compress(scan("&edoc_values",i,'#'));

      output;

      i = i +1;

end;

run;

proc transpose data=edoc_temp_2 out=edoc_temp_3(drop=_NAME_);

var temp_val;

id temp_var;

run;

Super User
Super User
Posts: 6,502

Re: create a dataset from a separated list

Glad it works, but that still looks very confusing.

You seem to have values in rows in a dataset that you convert to delimited strings that you then use to search for values in rows of another dataset.....

Can't you just merge the two datasets and stop using macro variables to pass data?

Can you post examples of the input and output?

Regular Contributor
Posts: 229

Re: create a dataset from a separated list

Your solution in your last post is shorter and works also.

But I have to take in account the input format can change.

%let CtxFormat="CtxName;CtxValue";

%let Ctx = "LotId;P110123; DuploWaferId;D02; Operation; 1000-OxideDepostion!1!0";

Now it is simple, but it has to work also for:

%let CtxFormat="CtxName;CtxEdit;CtxValue";

%let Ctx = "LotId;P110123;No; DuploWaferId;D02;Yes; Operation; 1000-OxideDepostion!1!0;Yes";

So with the CtxEdit no/Yes I will have to do something else.

With ctxName and ctxValue I will have to save them in the end. So I can not really hardcode it.

I have to find the place of CtxName and CtxValue in the format so I can use that place to find the same value in Ctx.

It is not so straight forward that place 1 will be always name and place 2 will be value

Super User
Super User
Posts: 6,502

Re: create a dataset from a separated list

Still not sure what you want.  If you just want to split the string into variable/value pairs and create a dataset then you can use proc transpose to assign the variable names.

%let Ctx = "LotId;P110123; DuploWaferId;D02; Operation; 1000-OxideDepostion!1!0";

data vertical ;

  length var $32 value $200;

  do _n_=1 by 2 until (var=' ');

    var = scan(&ctx,_n_,';','Q');

    value = scan(&ctx,_n_+1,';','Q');

    if var ne ' ' then output;

  end;

run;

proc transpose data=vertical out=want(drop=_Smiley Happy ;

  id var;

  var value;

run;

proc print;

run;

                   Duplo

Obs     LotId     WaferId           Operation

1     P110123      D02      1000-OxideDepostion!1!0


Regular Contributor
Posts: 229

create a dataset from a separated list

It will not be always Name Value

It could be Value Name

or Name Value Other

but he gives the format also in parameter (check first post)

but i solved it, thanks to all

Ask a Question
Discussion stats
  • 6 replies
  • 209 views
  • 0 likes
  • 3 in conversation