Date: Thu, 5 Jun 2008 05:40:33 +0530
Reply-To: ajay ohri <ohri2007@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: ajay ohri <ohri2007@GMAIL.COM>
Subject: Re: OT: Chance to Make SAS-L History: Did You Know That...
In-Reply-To: <c2192a610806041030m49d6f7f9y2794bf8ba6daa4e6@mail.gmail.com>
Content-Type: text/plain; charset=WINDOWS-1252
or you can use proc transreg (with _all_ option) and it will create the
variables for you.
On 6/4/08, SAS_learner <proccontents@gmail.com> wrote:
>
> Hello Mike,
>
> Here small trick I learnt
>
> Suppose we want to create macro variables for each of the treatment groups
> in our study. Suppose our study has 4 treatment groups. We can create
> these macro variables in the following way:
>
> proc sql;
>
> select count(distinct subjno) into :trt1
>
> from demog
>
> where treat=1;
>
> select count(distinct subjno) into :trt2
>
> from demog
>
> where treat=2;
>
> select count(distinct subjno) into :trt3
>
> from demog
>
> where treat=3;
>
> select count(distinct subjno) into :trt4
>
> from demog
>
> where treat=4;
>
> quit;
>
> %put trt1=&trt1 trt2=&trt2 trt3=&trt3 trt4=&trt4;
>
> The main drawback to creating macro variables this way with SQL is that
> there are alot of leading blanks in the macro variables. Specifically, the
> output macro variable will be 8 characters long. So a one digit number
> will
> have 7 leading blanks; a two digit number will have 6 leading blanks; etc.
> Our put statment for the example above would look like this:
>
> trt1= 8 trt2= 10 trt3= 4 trt4= 11
>
> A quick way to get rid of these leading blanks is simply to remake the
> macro
> variables with a %let statement:
>
> %let trt1=&trt1; %let trt2=&trt2; %let trt3=&trt3;
>
> %let trt4=&trt4;
>
> or
>
> %macro trimblank;
>
> %do i=1 %to 4;
>
> %let trt&i=&&trt&i;
>
> %end;
>
> %mend trimblank;
>
> %trimblank;
>
> Now our put statement would look like this:
>
> %put trt1=&trt1 trt2=&trt2 trt3=&trt3 trt4=&trt4;
>
> trt1=8 trt2=10 trt3=4 trt4=11
>
>
>
> Alternately, we can create the same macro variables with the following
> code.
>
> proc sql;
>
> select count(distinct subjno) into :trt1 - :trt4
>
> from demog
>
> group by treat;
>
> quit;
>
> For some reason macro variables created with SQL this way, do NOT seem to
> have the leading blanks. For this code, however, it is important that ALL
> the treatment groups have at least 1 subject. Since SQL assigns the values
> of the macro variables in the order they are encountered, if our data has
> only values for treat=1,2,4 then the count for treat=4 would be assigned to
> macro variable trt3 and macro variable trt4 would be blank.
>
> A quick validation check can be built into the code, to check that all
> treatment groups are represented. We can add a put statement to print an
> ERROR to the log if there is a problem.
>
> proc sql;
>
> select case
>
> when count(distinct treat) gt 4 then "ERROR"
>
> when count(distinct treat) lt 4 then "ERROR"
>
> else " "
> into :chk
>
> from demog;
>
> select count(distinct subjno) into :trt1 - :trt4
>
> from demog
>
> group by treat;
>
> quit;
>
> %put &chk trt1=&trt1 trt2=&trt2 trt3=&trt3 trt4=&trt4;
>
> If there is a problem, our put statement will look like this:
>
> ERROR trt1=8 trt2=10 trt3=11 trt4=
>
> If all is well, our put statement will look like this:
>
> trt1=8 trt2=10 trt3=4 trt4=11
>
>
> On Wed, Jun 4, 2008 at 9:41 AM, <ohri2007@gmail.com> wrote:
>
> > Hi List,
> >
> >
> > here goes.- A lot of SAS programmers run aggregate level stats on big
> > datasets,put them in an excel file , and email it to marketing
> > /business heads who read it (!). This can take a lot of time if you
> > are firing queries remotely. So here is a automating program that does
> > the aggregation, puts it in a spreadsheet and sends it back in an
> > email. You can configure for any repeatable task that you need.
> >
> > Regards,
> >
> > Ajay
> >
> > Steps in preparing a report
> >
> > * Import data from data sources (spreadsheets, databases mostly
> > .Here assuming BASE SAS ,only csv files are considered)
> > * Manipulating data for analysis (in the case below the only data
> > manipulation done is removal of duplicates. This section can be
> > changed as needed by user for
> > o Frequency counts using Proc Freq.
> > o Mean, maximum and minimum counts using Proc Means.
> > o Customized reports using Proc Report Procedure.)
> > * Presenting and formatting data
> > * Output data in the form of a report /spreadsheet.
> > * Emailing internal customers of the report.
> >
> > ———————————————————————————————————
> >
> > Using BASE SAS, the following can be coded as:
> >
> > /*PROGRAM TO AUTOMATE REPORTING */
> >
> > /*DECLARING THE PATH OF THE INPUT/OUTPUT FOLDER */
> >
> > %let pathfile = 'X:\Auto\AUG06-AUTOS\' ;
> >
> > /*CREATING LIBRARY NAME */
> >
> > libname auto &pathfile;
> >
> > run;
> >
> > /*TO CONSERVE SPACE*/
> >
> > options compress=yes;
> >
> > /*TO MAKE LOG READABLE */
> >
> > options macrogen symbolgen;
> >
> > /* STARTING MACRO FOR REPEATABLE EXECUTION OF REPORT*/
> >
> > %macro impmth(mth,num,emailid);
> >
> > /*MANIPULATING VARIABLES TO REDUCE CHANGES */
> >
> > data _null_ ;
> >
> > call symput('filepath',"'"||&pathfile||&mth||'.csv'||"'" );
> >
> > call symput('unqpath',"'"||&pathfile||"unq"||&mth||'.csv'||"'" );
> >
> > call symput('unqxls',"'"||&pathfile||"unq"||&mth||'.xls'||"'" );
> >
> > run;
> >
> > /*IMPORT*/
> >
> > /*IMPORTING DATA FROM CSV FILES STORED IN FOLDER DECLARED ABOVE*/
> >
> > PROC IMPORT OUT= auto.&num
> >
> > DATAFILE= &filepath
> >
> > DBMS=CSV REPLACE;
> >
> > GETNAMES=YES;
> >
> > DATAROW=2;
> >
> > quit;
> >
> > /*CREATING VARIABLE FOR DISTINGUISHING INPUT*/
> >
> > data auto.#
> >
> > set auto.#
> >
> > &num =1;
> >
> > run;
> >
> > ———————————————————————–
> >
> > /*DATA MANIPULATION HERE REMOVING DUPLICATES*/
> >
> > proc sort data=auto.&num nodupkey;
> >
> > by REFAGREEMENTID;
> >
> > run;
> >
> > ———————————————————————–
> >
> > /*EXPORT*/
> >
> > /*EXPORTING DATA TO A CSV FILE*/
> >
> > PROC EXPORT DATA= AUTO.&num
> >
> > OUTFILE= &unqpath
> >
> > DBMS=CSV REPLACE;
> >
> > RUN;
> >
> > /*EXPORTING DATA TO A XLS FILE*/
> >
> > ODS HTML FILE=&unqxls;
> >
> > proc print data=auto.#
> >
> > run;
> >
> > ODS HTML CLOSE;
> >
> > /*EMAILING THE RESULTS*/
> >
> > filename outbox email &emailid;
> >
> > data _null_ ;
> >
> > file outbox
> >
> > to=(&emailid)/* Overrides value in filename statement */
> >
> > cc=(&emailid)
> >
> > subject='File from'
> >
> > attach=(&unqxls)
> >
> > ;
> >
> > put 'Hi,';
> >
> > put 'This is an integrated approach to automating reports';
> >
> > put 'It works great!';
> >
> > run;
> >
> > %mend;
> >
> > /*CALLING THE MACRO*/
> >
> > /*HERE 'Ahmedabad' IS THE INPUT FILE WHILE Ahd IS THE OUTPUT FILE*/
> >
> > /*HERE 'aohri@hotmail.com' IS THE EMAIL ID TO BE MAILED THE REPORT*/
> >
> > %impmth('Ahmedabad',Ahd,'aohri@hotmail.com');
> >
> >
> > On 6/4/08, Nat Wooding <Nathaniel.Wooding@dom.com> wrote:
> > > Miiiiiiiiiikkkkkkkkkkkeeeeeeeee
> > >
> > > Aren't we anticipating St Crispian's Day a bit early? It's not til
> > October
> > > 25. But, I'll forgive you since this thread needs to stay alive, like
> the
> > > memory of King Harry.
> > >
> > > http://www.youtube.com/watch?v=OAvmLDkAgAM
> > >
> > > Nat
> > >
> > >
> > >
> > >
> > >
> > > Nat Wooding
> > > Environmental Specialist III
> > > Dominion, Environmental Biology
> > > 4111 Castlewood Rd
> > > Richmond, VA 23234
> > > Phone:804-271-5313, Fax: 804-271-2977
> > >
> > >
> > >
> > > Michael Raithel
> > > <michaelraithel@W
> > > ESTAT.COM>
> > To
> > > Sent by: "SAS(r) SAS-L@LISTSERV.UGA.EDU
> > > Discussion"
> > cc
> > > <SAS-L@LISTSERV.U
> > > GA.EDU>
> > Subject
> > > OT: Chance to Make SAS-L
> History:
> > > Did You Know That...
> > > 06/04/2008 08:49
> > > AM
> > >
> > >
> > > Please respond to
> > > Michael Raithel
> > > <michaelraithel@W
> > > ESTAT.COM>
> > >
> > >
> > >
> > >
> > >
> > >
> > > Dear SAS-L-ers,
> > >
> > > It's baaa_aaack! Here is another chance to join the thread that swept
> > > though the 'L and provided countless SAS programming tips to fellow
> > > SAS-L-ers. Here is the original 2006 posting with a new SAS tip from
> > > this particular poster.
> > >
> > > This is it! This is _YOUR_ chance to help make SAS-L history! You can
> > > do so by joining me in making this the longest thread that has ever
> > > existed in the storied history of this happy medium that we call:
> > > SAS-L. In doing so, you will not only help to make SAS-L history, you
> > > will also be an integral part of it!
> > >
> > > All that you have to do is contribute a single SAS tip to this thread.
> > > It doesn't necessarily have to be something profound; just solid and
> > > accurate.
> > >
> > > This opportunity is good for prolific posters, for occasional posters,
> > > for once-in-a-blue-moon posters, and especially for lurkers who have
> > > never posted before. Everybody on this list knows _SOMETHING_ about
> > > SAS. So, pick your best tip and post it--even if you are sure that
> > > others already know it. It could be that somebody doesn't know it, or
> > > that you refresh somebody's memory.
> > >
> > > So, here is how this will work. You simply write: "Did you know that:
> > > " and follow it up with your brief SAS tip. It couldn't be any easier,
> > > could it?
> > >
> > > Okay, so I'll get the ball rolling. Here goes:
> > >
> > > Say that you have SAS/CONNECT software and want to download a _LARGE_
> > > SAS data set from your UNIX server to your Workstation. However, you
> do
> > > not need the entire _LARGE_ SAS data set for your analysis; simply the
> > > observations that contain data for the subset that you are analyzing.
> > > You can apply a WHERE statement (or WHERE data set option) to your PROC
> > > DOWNLOAD, like this:
> > >
> > > proc download data=TwoHundredYearHistoryFile
> > > out=HistoryForNineteenSeventryThree;
> > > where year = 1973;
> > > run;
> > >
> > > ...or...
> > >
> > > proc download data=TwoHundredYearHistoryFile(where=(year = 1973))
> > > out=HistoryForNineteenSeventryThree;
> > > run;
> > >
> > > ...so the HistoryForNineteenSeventryThree SAS data set will only have
> > > the subset of observations from the large SAS data set where the year
> is
> > > equal to 1973.
> > >
> > > Hey, that was easy. Now, it is your turn!
> > >
> > > If you need more incentive to participate, then consider these
> inspiring
> > > words:
> > >
> > > From this day to the ending of the world,
> > > But we in it shall be remember'd;
> > > We few, we happy few, we band of SAS-L-ers;
> > > For s/he to-day that posts with me shall be my brother/sister;
> > >
> > > I'll bet that did it. Okay, now get posting!
> > >
> > > +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > > Michael A. Raithel
> > > "The man who wrote the book on performance"
> > > E-mail: MichaelRaithel@westat.com
> > >
> > > Author: Tuning SAS Applications in the MVS Environment
> > >
> > > Author: Tuning SAS Applications in the OS/390 and z/OS Environments,
> > > Second Edition
> > > http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=58172
> > >
> > > Author: The Complete Guide to SAS Indexes
> > > http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=60409
> > >
> > > +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > > Be great in act, as you have been in thought. - William Shakespeare
> > > +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > >
> > >
> > > -----------------------------------------
> > > CONFIDENTIALITY NOTICE: This electronic message contains
> > > information which may be legally confidential and/or privileged and
> > > does not in any case represent a firm ENERGY COMMODITY bid or offer
> > > relating thereto which binds the sender without an additional
> > > express written confirmation to that effect. The information is
> > > intended solely for the individual or entity named above and access
> > > by anyone else is unauthorized. If you are not the intended
> > > recipient, any disclosure, copying, distribution, or use of the
> > > contents of this information is prohibited and may be unlawful. If
> > > you have received this electronic transmission in error, please
> > > reply immediately to the sender that you have received the message
> > > in error, and delete it. Thank you.
> > >
> >
>
| 
