BookmarkSubscribeRSS Feed
Filipvdr
Pyrite | Level 9

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

6 REPLIES 6
andreas_lds
Jade | Level 19

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.

Filipvdr
Pyrite | Level 9

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;

Tom
Super User Tom
Super User

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?

Filipvdr
Pyrite | Level 9

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

Tom
Super User Tom
Super User

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


Filipvdr
Pyrite | Level 9

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

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
  • 6 replies
  • 978 views
  • 0 likes
  • 3 in conversation