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;
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.
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;
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?
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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.