taking a single string and converting that to single column in table

[CPAZ]

I have this code below, it works but not entirely. It works for the first 200 characters of the string, but not the rest. Is there a way to take the complete string? Thanks

%macro getqst2(wafs=);
   

data work.wafs;
length wafer_id $4;
varlist=symget('wafs');
do until (wafer_id=' ');
i+1;
wafer_id=scan(varlist,i);
if wafer_id ne '' then output;
end;
drop i varlist;
run;

%mend;

wafs is passed to the macro, and here is the string: (a complete string delimited by space): 

PJP2 PJ35 PMDH PH6K PHCY PG23 PF6X PH66 PH5T PHTU PJ3Y PJ74 PHDF PLLH PJ7C PLWA PJ41 PJ3N PHJ4 PJ9H PHYA PHHP PHN8 PHTK PMR8 PMJ5 PJ55 PJ3J PMN1 PKWT PHTL PMA4 PMGH PHE1 PM69 PLY5 PJ75 PHJD PM1H PM6L PJ3T PJAY PJF8 PMJA PJHW PJF9 PHDU PL9W PJ5J PLX3 PHTE PJRH PJEY PJ34 PHP7 PHEN PJRK PN2J PJRL PJ5F PJT9 PJPA PJRM PJ57 PJ3L PHPT PMKJ PHXA PMWE PHEM PJED PJRN PJ12 PJ1K PJ39 PJ62 PJ33 PJHK PM9F PHPU PJ5E PMXP PLJJ PMXK PHJJ PJEW PL7D PJ3P PMPK PHG8 PN2L PMPF PHX9 PM71 PJ5K PJ15 PMGG PM5U PHK6 PJ7U PN5C PJ61 PH8L PM65 PMYJ PM62 PLX8 PL2A PJ6W PHNN PLXY PMJK PMUJ PJ3R PLNH PH94 PJ6G PMJ8 PMMA PMLA PMM1 PKY8 PJ4Y PJG9 PMLL PJGH PJRT PHLC PJ7A PM4D PM21 PH6J PMKM PHF1 PJ96 PHDE PHYL PMGC PJ43 PJH6 PJNW PJ2W PJCM PM7D PN4H PLXJ PHD4 PJ7R PJAH PJEX PJ8D PHK9 PHPN PHD8 PPKT PMDK PJKG PHTJ PHD5 PLDG PMLH PPT3 PP92 PRGR PP5G PMDC PHDG PLER PMPE PMHU PJF2 PNM5 PJF3 PR86 PHT9 PP8P PT3W PN1R PP83 PN2M PM2X PMLE PHDM PL14 PJ3K PMPC PMPJ PJ68 PJGA PH97 PJNR PJFP PJAT PLXD PHJU PHKL PLXM PMCW PMCH PMAH PL23 PKUR PHCL PHGM PN4K PMJC PN49 PJ56 PMC8 PMX6 PMC7 PN4P PM6N PN4D PN4C PRT4 PMYP PHGE PN23 PR2F PHW9 PRKX PRP9 PPWX PNML PT7T PR3J PR14 PRC9 PPRJ PN5F PP68 PMJR PR1Y PHGD PJ51 PHE6 PJN2 PH5Y PMKW PJAL PJE1 PPM5 PHP6 PHJ7 PJ9Y PN1Y PMH4 PMJT PNNF PMCA PHAW PPXU PM8W PMJ6 PP81 PMGE PMKK PMDD PPYG PPP1 PH93 PMT1 PHH3 PNXE PP9H PN46 PMCL PHU7 PNP5 PRHD PRKJ PHKX PJ69 PMC5 PRD2 PHP4 PHUA PN4U PNL8 PRL9 PMFN PNAR PNMX PT7D PN1L PN53 PHDN PMUA PM97 PNKY PPK6 PNFN PHKM PNRE PLWU PNKX PJ6E PNGT PLXU PN56 PHWC PR6M PN4G PP22 PR7T PH6D PNU5 PMHH PJ86 PNFX PPFL PHA5 PMKN PJP7 PNL7 PJ3X PM6E PNAU PN7J PMGM PPL3 PNLU PMWC PRHA PHJ8 PN6P PRXM PNM7 PRMC PNTL PLXX PP67 PP2T PPKP PPPJ PPXW PN9M PMKX PPLU PNJJ PLY7 PH89 PRC6 PT2E PPYT PNMG PH6A PPKA PLDD PHFU PHU9 PNL6 PR13 PM9L PPYL PHY8 PPNW PT6Y PNLD PRHJ PTDP PML1 PRL3 PJA3 PMLU PRJN PN7K PPRP PP85 PRUH PRJP PR9J PLX6 PLJE PRFY PPPL PHA3 PJ3A PR9E PR4Y PPGH PN6E PPEP PNGW PPEN PRXN PRXR PRHY PPER PJ19 PPDH PYUE PN1H PUYM PJ5M PRCP PR71 PYW9 PHH4 PMJP R114 PPTK PHRF PU74 PYUF PUUC PUN5 PNGL PUXD PNEY PU3Y PY6A PW71 PNPH PNPT PRRM PNLN PUJC PTFY PPR4 PHDL PPM7 PRU6 PMXJ PMXT PMXU PN4A PPRX

[Ksharp · Accepted solution]

That is because the defalut length of character is 200, so you need to assign it a more long length.

%macro getqst2(wafs=);

data work.wafs;

length wafer_id $4 varlist $ 32767;

varlist=symget('wafs');

do until (wafer_id=' ');

i+1;

wafer_id=scan(varlist,i);

if wafer_id ne '' then output;

end;

drop i varlist;

run;

%mend;

Ksharp

[CPAZ]

Thanks KSharp and all. All of these soultions work. I do not see an obvious way to tag this discussion as "answered".

[Linlin]

Hi CPAZ,

after you select one as correct answer and two as helpful answers then the discussuon is marked as "answered".

[CPAZ]

Hi Linlin, Sorry for the stupid reply, but I do not see how to mark the replies as "correct answer". I am logged in but still do not see how to do it. Thanks, Chris

[Linlin]

Hi Chris,

After you log in, look for the green star, then click it.

[CPAZ]

Ok. There must be something funky going on with either my profile or something else. I do not see a green star at all anywhere on the page. I am logged. I will try to contact the webmaster. Thanks. Chris

[Patrick]

I'm possibly missing something here, but: Why in first place do you need to assign the macro variable to a SAS variable? Wouldn't code like below work as well?

%macro getqst2(wafs=);
  data work.wafs;
    length wafer_id $20;
    do until (wafer_id=' ');
      _i+1;
      wafer_id=scan("&wafs",_i);
      if wafer_id ne '' then output;
    end;
    drop _:;
  run;
%mend;

%getqst2(wafs=PJP2 PJ35 PMDH PH6K PHCY PG23)

proc print data=work.wafs;
run;

...and in case above code is o.k: Do you really need a macro or would code like below be sufficient?

/* without a macro */

%let wafs=PJP2 PJ35 PMDH PH6K PHCY PG23;

data work.wafs2;
  length wafer_id $20;
  do until (wafer_id=' ');
    _i+1;
    wafer_id=scan("&wafs",_i);
    if wafer_id ne '' then output;
  end;
  drop _:;
run;

proc print data=work.wafs2;
run;

[FriedEgg]

Another macro approach, if you list were to ever exceed ~6553 wafer_id's the data step will not work since it will be longer the 32,767.  This approach will work for up to about 13,106 wafer_id's since the max length for macro variable is 65,534.

%macro getqst2(wafs=);

proc sql;

  create table wafs ( wafer_id char(4) );

  %do i=1 %to %sysfunc(countw(&wafs));

   insert into wafs ( wafer_id ) values ( "%scan(&wafs,&i)" );

  %end;

quit;

%mend;

%getqst2(wafs=PJP2 PJ35 PMDH PH6K PHCY PG23 PF6X PH66 PH5T PHTU PJ3Y);

[FriedEgg]

Here is one more method, using array, just because.

%let wafs=PJP2 PJ35 PMDH PH6K PHCY PG23 PF6X PH66 PH5T PHTU PJ3Y;

data wafs;

  array w[%sysfunc(countw(&wafs))] $ _temporary_ ("%sysfunc(tranwrd(&wafs,%str( ),%str(" ")))");

  do _n_=1 to dim(w);

   wafer_id=w[_n_];

   output;

  end;

run;

[CPAZ]

Thanks all of these suggestions work. I am figuring out why I can't see the selection for "correct answer" or "Helpful hint". I woul dlike ot give credit for those who have helped.

Thanks, Chris

[FriedEgg]

One last example.  Some may find this interesting as a fairly unique approach.  I will use the APP functions to insert the string to the array.

%let wafs=PJP2 PJ35 PMDH PH6K PHCY PG23 PF6X PH66 PH5T PHTU PJ3Y;

data wafs;

  array w[%sysfunc(countw(&wafs))] $ 5 _temporary_;

  call pokelong("&wafs",addrlong(w[1]));

  do _n_=1 to dim(w);

   wafer_id=w[_n_];

   output;

  end;

run;

I normally do not share code using these 'controversial' functions but as I use them more and more in my own production work I feel more compelled to share possible uses for them.  At the SAS Global Forum this year Paul Dorfman and William Viergever have an excellent paper detailing uses of these functions, there are a few other papers about them as well which I highly recommend to anyone concerned with advanced SAS programming to read.

Straight from Memory: ADDR, PEEK, POKE as SAS Programming Tools

Paul Dorfman, Paul Dorfman Consulting

William Viergever, Viergever & Associates

[Linlin]

Hi FE,

Thank you for sharing. I will definitely read the paper. - Linlin

[Haikuo]

FriedEgg,

Do you know when the full-text paper will be published online?

Thanks,

Haiuo

[FriedEgg]

Pretty much all other papers for this years proceedings are already avaiable here:

http://support.sas.com/resources/papers/proceedings12/

Direct to the paper I mentioned:

http://support.sas.com/resources/papers/proceedings12/223-2012.pdf