Date: Thu, 19 Feb 2009 18:04:13 -0600
Reply-To: Joe Matise <snoopy369@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Joe Matise <snoopy369@GMAIL.COM>
Subject: Re: Question on how to extend this to multiple variables
In-Reply-To: <09fe01c992ed$986a5660$832fa8c0@HP82083701405>
Content-Type: text/plain; charset=ISO-8859-1
You could also try a DoW sort of loop (not sure it actually qualifies, but
maybe?):
%macro keeper(var);
description = " ";
do _n_=1 to 4;
set test(keep=&var);
variable = "&var";
description = catx(' ',description,&var);
end;
output;
%mend keeper;
data data_master_header_2;
retain description;
format description $50.;
format variable $50.;
keep variable description;
%keeper(age_group);
%keeper(rs800292);
%keeper(rs2230199);
run;
And then use a proc sql select into from dictionary.columns (or a proc
contents output), to create the %keeper calls. I have my doubts as to how
efficient this would be, but it might be faster than proc transpose since
there are so few rows in your dataset but many columns.
-Joe
On Thu, Feb 19, 2009 at 5:55 PM, Mary <mlhoward@avalon.net> wrote:
> Yes, that is what I'm doing; I'll give yours a try, as
> it seem shorter than the approach I was trying.
>
> I tried making a macro out of the code below, and it
> is working, but does take a little while:
>
> %macro get_strings;
>
> data data_dictionary;
> informat variable_name $40. description $100.;
> stop;
> run;
>
> proc datasets library=work;
> delete position;
> quit;
> ods output position=position;
> proc contents varnum data=test;
> run;
> proc sql noprint;
> select max(num) into :maxnum
> from position;
> quit;
>
> %do i=1 %to &maxnum;
>
> proc sql noprint;
> select variable into :varname
> from position
> having num=&i;
> quit;
>
> proc sql noprint;
> select trim(&varname) || ' ' into :description separated by ' '
> from test
> where trim(&varname) ^='';
> quit;
> %put &description;
> data out1;
> informat variable_name $40. description $100.;
> variable_name="&varname";
> description="&description";
> run;
>
> proc append base=data_dictionary data=out1;
> run;
>
> %end;
>
> %mend;
>
> %get_strings;
>
> Thanks!
>
> -Mary
>
> ----- Original Message -----
> *From:* Joe Matise <snoopy369@GMAIL.COM>
> *To:* SAS-L@LISTSERV.UGA.EDU
> *Sent:* Thursday, February 19, 2009 5:41 PM
> *Subject:* Re: Question on how to extend this to multiple variables
>
> I'm not entirely sure I understand precisely what you're doing (is this
> effectively parsing a multiline column header into a single
> label/description?), but what about this?
>
>
> proc transpose data=test out=data_header_2;
> var _all_;
> run;
>
> data data_master_header_2;
> set data_header_2;
> description = catx(' ',col1,col2,col3,col4);
> variable = _name_;
> keep description variable;
> run;
>
> -Joe
>
>
> On Thu, Feb 19, 2009 at 5:05 PM, Mary <mlhoward@avalon.net> wrote:
>
> > Or alternatively, a way to do this SQL without having
> > to hard code in the variables seems to get me there:
> >
> > proc sql noprint;
> > select trim(age_group) || ' ' into :age_group separated by ' '
> > from test
> > where trim(age_group) ^='';
> > quit;
> > %put &age_group;
> >
> > proc sql noprint;
> > select trim(rs800292) || ' ' into :rs800292 separated by ' '
> > from test
> > where trim(rs800292) ^='';
> > quit;
> > %put &rs800292;
> >
> > proc sql noprint;
> > select trim(rs2230199) || ' ' into :rs2230199 separated by ' '
> > from test
> > where trim(rs2230199) ^='';
> > quit;
> > %put &rs2230199;
> >
> >
> > data data_master_header_2;
> > age_group="&age_group";
> > rs800292="&rs800292";
> > rs2230199="&rs2230199";
> > run;
> >
> >
> > -Mary
> >
> >
> > ----- Original Message -----
> > From: Mary
> > To: SAS-L@LISTSERV.UGA.EDU
> > Sent: Thursday, February 19, 2009 4:44 PM
> > Subject: Question on how to extend this to multiple variables
> >
> >
> > Note this is a *question*, not an answer, though sometimes if I post a
> > question I can think of an answer, but I'd appreciate it if anyone else
> > would try to think of an answer as well!
> >
> > I've originally got an Excel file that has the description of the
> > field in the same column as the field names and then the data.
> > I've pulled those header columns, and put the variable names
> > in the first row, and then I have saved it as text, and have
> > data like the sample below.
> >
> > What I'm after is to write a program to create the data dictionary
> > from this file, since it changes often along with the variable names
> > and descriptions.
> >
> > I've got it going for one variable, as shown below, age_group.
> >
> > ****
> > Question: How can I modify the code in data_master_header_2
> > to aggregate for all variables (in my real data, I've got over 300,
> > so I don't want to name variable names).
> >
> > Or other code that produces one record from the up to 4 possible
> > records that is the concatenation of all 4 is what I'm after, kind
> > of like one summary record except a concatenation of the records
> > rather than a sum.
> >
> > *****
> >
> >
> >
> > data test;
> > informat age_group $50. rs800292 $50. rs2230199 $50.;
> > infile cards missover delimiter='^';
> > input age_group rs800292 rs2230199;
> > cards;
> > ^CFH^ C3
> > Age ^ IVS1/EX2^Ex2
> > Group ^ ^rs2230199
> > ^ rs800292^
> > ;
> > run;
> >
> > data data_master_header_2;
> > informat age_group $40. prev_age_group $40.;
> > format age_group $40. prev_age_group $40.;
> > length age_group $ 40 prev_age_group $ 40;
> > set test(firstobs=1 obs=4);
> > retain age_group prev_age_group;
> > if _N_=1 then
> > do;
> > age_group=age_group;
> > prev_age_group=age_group;
> > end;
> > else
> > do;
> > age_group=trim(prev_age_group) || ' ' || trim(age_group);
> > prev_age_group=age_group;
> > end;
> > if _N_ = 4 then output;
> > keep age_group;
> > run;
> >
> > data out2;
> > informat variable_name $30. description $50.;
> > set data_master_header_2;
> > array vararray{*} age_group;
> > do i=1 to dim(vararray);
> > variable_name = vname(vararray[i]);
> > description = vararray[i];
> > output;
> > end;
> > keep variable_name description;
> > run;
> >
> > -Mary
> >
>
>
| 
