topic Re: create a dataset from a separated list in SAS Programming

create a dataset from a separated list

Filipvdr — Wed, 05 Oct 2011 08:34:06 GMT

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

Re: create a dataset from a separated list

andreas_lds — Wed, 05 Oct 2011 11:32:43 GMT

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

LotId	DuploWaferId	Operation
P110123	D02	1000-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.

create a dataset from a separated list

Filipvdr — Wed, 05 Oct 2011 11:47:24 GMT

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;

Re: create a dataset from a separated list

Tom — Wed, 05 Oct 2011 12:00:52 GMT

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=_:) ;

id var;

var value;

run;

proc print;

run;

Duplo

Obs LotId WaferId Operation

1 P110123 D02 1000-OxideDepostion!1!0

create a dataset from a separated list

Filipvdr — Wed, 05 Oct 2011 12:12:21 GMT

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

Re: create a dataset from a separated list

Tom — Wed, 05 Oct 2011 13:26:37 GMT

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?

Re: create a dataset from a separated list

Filipvdr — Thu, 06 Oct 2011 05:53:28 GMT

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