Date:         Thu, 1 May 1997 00:51:47 GMT
Reply-To:     Adam Hendricks <ahendrix@AA.NET>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Adam Hendricks <ahendrix@AA.NET>
Organization: Alternate Access Inc.
Subject:      Re: ***** I need help urgently ******

In article <3367610A.768A@patriot.net>, trogers@patriot.net says...
>
>I have a data set with 7,033 obs, the following is a sample data set:
>
>siteid  agncyabr
>1646     cd
>1646     fsa
>1646     nrcs
>1972     fsa
>1972     nrcs
>1981     rd
>1981     fsa
>1990     rd
>
>I want to create a new variable called 'colocate'. If the the agncyabr
>has the same siteid as other agncyabr's, then I want the out put dataset
>to look like the following.
>
>siteid  agncyabr  colocate
>1646     cd       cd fsa nrcs
>1646     fsa      cd fsa nrcs
>1646     nrcs     cd fsa nrcs
>1972     fsa      fsa nrcs
>1972     nrcs     fsa nrcs
>1981     rd       fsa rd
>1981     fsa      fsa rd
>1990     rd       rd
>
>I appreciate any help, greatly.
>--
>******************************************************************
>* Tim Rogers                              V-Mail:  703-689-0595  *
>* Associate Consultant                       Fax:  703-968-0595  *
>* Advantage Systems Group Inc.  E-Mail:  trogers@novarealty.com  *
>* 1-800-484-5189 code 8077               trogers@patriot.net     *
>******************************************************************
Here's a way that works provided that you hold to the $4 length for
the agncyabr field and that no colocate field will contain more than
40 abbreviations:
 
options nocenter nodate nonumber;
 
data start;
  length siteid 8 agncyabr $4;
  input siteid  agncyabr $;
cards;
1646     cd
1646     fsa
1646     nrcs
1972     fsa
1972     nrcs
1981     rd
1981     fsa
1990     rd
;
run;
 
proc sort data=start;
  by siteid agncyabr;
run;
 
proc print data=start;
  title 'Before';
run;
 
proc transpose data=start
               out=middle;
  by siteid;
  var agncyabr;
run;
 
proc sql noprint;
  select put(count(*),2.) into :cols
  from sashelp.vcolumn
  where libname = 'WORK'   and
        memname = 'MIDDLE' and
        name like 'COL%';
 
data _null_;
  length lenc $4;
  cols = compress("&cols");
  len  = &cols*5;
  lenc = put(len, 2.);
  lenc = compress('$'||len);
  call symput('len', lenc);
  call symput('cols', cols);
run;
 
data middle(keep=siteid colocate);
  length colocate &len;
  set middle;
  array _c (*) col1-col&cols;
  do i = 1 to dim(_c);
    colocate = compbl(colocate||_c{i});
  end;
run;
 
proc sql;
  create table end as
  select a.siteid, a.agncyabr, b.colocate
  from start  a,
       middle b
  where a.siteid = b.siteid
  order by 1, 2;
 
proc print data=end;
  title 'After';
run;
 
Before
 
OBS    SITEID    AGNCYABR
 
 1      1646       cd
 2      1646       fsa
 3      1646       nrcs
 4      1972       fsa
 5      1972       nrcs
 6      1981       fsa
 7      1981       rd
 8      1990       rd
After
 
OBS    SITEID    AGNCYABR    COLOCATE
 
 1      1646       cd        cd fsa nrcs
 2      1646       fsa       cd fsa nrcs
 3      1646       nrcs      cd fsa nrcs
 4      1972       fsa       fsa nrcs
 5      1972       nrcs      fsa nrcs
 6      1981       fsa       fsa rd
 7      1981       rd        fsa rd
 8      1990       rd        rd