About Howles

Howles · ‎06-01-2012

Reeza and Haikuo both use the ALTER statement. I believe that this implicitly causes the creation of a new table to replace the old one. This can be demonstrated by toggling the REPLACE system option to NOREPLACE before invoking ALTER. Mike.Davis wrote: Thank you all. LinLin's method and Reeza and Haikuo's methods all works, LinLin's method create a new dataset but the other not, except this what is the other main difference? Thanks

Howles · ‎06-01-2012

See http://www.sascommunity.org/wiki/PROC_MEANS_-_Improve_on_the_default

Howles · ‎05-12-2012

Seems to work as expected for me. First create test tables: proc sql ; create table inserted_table like sashelp.class ; create table source_table as select age, height, name, sex from sashelp.class where age EQ 12 ; quit ; Now define macro: %macro do_insertions(c=) ; insert into inserted_table(&c) select &c from work.source_table s ; %mend do_insertions ; Next use CALL EXECUTE to trigger the macro: data _null_ ; if _n_ EQ 1 then call execute('proc sql ;') ; call execute('%do_insertions( c=%str(name, sex, age) )') ; run ; Finally, look at the results: select * from inserted_table ; quit ; Should see: Name Sex Age Height Weight ------------------------------------------- James M 12 . . Jane F 12 . . John M 12 . . Louise F 12 . . Robert M 12 . .

Howles · ‎05-08-2012

One method which assures exactly equal likelihoods: data notrandom ; do val = 1 to 5 ; do rep = 1 to 200 ; order = ranuni(2468) ; output ; end ; end ; run ; proc sort data=notrandom out=random(keep=val) ; by order ; run ; proc freq data=random ; run ; pma85 wrote: I'm trying to generate a data set with 1,000 observations with random integers from 1 to 5. Does anyone know how I would do this using the rand function? Here's the question posed: Create a temporary SAS data set (Random) consisting of 1,000 observations, each with a random integer from 1 to 5. Make sure that all integers in the range are equally likely. Run PROC FREQ to test this assumption

Howles · ‎05-01-2012

Or, avoid the PROC TRANSPOSE step by letting the ORDER BY clause and the KEEP= option do the work. select name into :varlist separated by ', ' from dictionary.columns where libname EQ 'SASHELP' and memname EQ 'ZIPCODE' order by name NE 'STATE', name NE 'CITY', varnum ; create table one(keep = state city statename--dst) as select &varlist from sashelp.zipcode ; Tom wrote: Here is a useful trick for getting variable lists expanded. Use PROC TRANSPOSE to make a dataset with the variable names. You can then pull the list into a macro variable. proc transpose data=sashelp.zipcode(obs=0) out=_names_; var state city statename--dst ; run; proc sql noprint ; select _name_ into :varlist separated by ', ' from _names_; create table one as select &varlist from sashelp.zipcode ; quit;

Howles · ‎04-21-2012

I try to let DATA step power do as much of the work as possible. Test data: data TABLE1; input VarA VarB VarC; cards; 34 45 13 ; data TABLE2; input City$ VarA VarB VarC; cards; Tor 64 34 12 Ott . 35 . Mon 47 . 9 ; Notice the use of the same names for corresponding variables in different tables. I usually find that advantageous. Step 1 is to create serial numbers for the TABLE2 observations: data numbered ; Serial + 1 ; set TABLE2 ; run ; This could be skipped if there were ordered keys in TABLE2. An alternative is to sort by city. Next is the tricky bit, crossing the single observation in TABLE1 with the vector of keys: data temp ; set numbered(keep = Serial City) ; if _n_ EQ 1 then set TABLE1 ; run ; The results looks like this: Serial City VarA VarB VarC 1 Tor 34 45 13 2 Ott 34 45 13 3 Mon 34 45 13 Finish with a simple UPDATE: data WANT ; update temp numbered ; by Serial ; YoY= (VarA / VarB)-1 ; run ;

Howles · ‎04-20-2012

If FriedEgg's theory is right, you should see multiple year values if you turn off the NOPRINT option. FriedEgg wrote: In your syntax the first rows value will be the value of the macro variable (both in 9.1 and 9.3) there must be something different with the ordering, in some way, observed between the two queries. If you always want to retrieve the most recent year you should rewrite this query anyway to ensure the you get the date you are looking for using either MAX() function or an ORDER BY statement. proc sql noprint; select year(min(campaign_end_date)), year(max(campaign_end_date)) into :min_year, max_year from sql.campaign where campaign_id eq "014100094"; quit;

Howles · ‎04-16-2012

It can get confusing. AA posts something. BB replies about some aspect. AA edits the post in a way which removes or alters the context for BB's reply. Things no longer make sense. I presume that one can reply to his or her own post. That should be sufficient. Arthur Tabachneck wrote: Howard, here I'm not so sure that I agree. Most of us have, at some time, posted something that we quickly realize is misleading/wrong/detrimental to someone trying to learn the language. I, personally, don't have a problem with the poster, in such cases, beomg able to delete their own post or at least being able to modify it so that it isn't misleading/wrong/detrimental. What I am concerned about is that I don't want them to have the capability of deleting other people's posts. Am I missing something? And, to clarify another point in this discussion, when someone deletes their own post, I think that they, personally, should lose the points they gained from adding the post that they decided to delete.

Howles · ‎04-16-2012

You can use PROC PRINTTO to redirect both the log and the listing to one file. proc printto log="&path\&target.txt" print="&path\&target.txt" ; Output and log content will be interleaved.

Howles · ‎04-08-2012

http://www.sascommunity.org/wiki/Look-Ahead_and_Look-Back

Howles · ‎04-02-2012

My guess is that the problem is one of arithmetic accumulation. The UPDATE and MODIFY statements can handle this, but it gets tricky if the variable to be incremented in the master data set and the increments in the transaction data set have the same name. A RENAME is needed. Example: data master ; input ID HowMuch ; cards ; 98 10 99 1000 ; data transaction ; input ID HowMuch ; cards ; 98 2 99 200 99 30 99 4 ; data master ; modify master transaction( rename = HowMuch=increment ) ; by ID ; HowMuch + increment ; run ; ZRick wrote: Do you know exactly what "an accumulation statement" is?

Howles · ‎04-02-2012

I don't know if efficiency is a concern. I do know that the WHERE approach will not generate an observation for a month with no negatives. That may or may not be desired. Linlin wrote: more efficient: data want; set have(where=(x<0)); by date; if first.date then total=0; total+x; if last.date then output; run;

Howles · ‎04-02-2012

So the data are not static ("will expand over time") and will live in two places (the individual data sets and the common one). That's parallel maintenance, something to be avoided. Consider making the common data set a view. Mike7085 wrote: I'm want to read all the SAS datasets in a directory into a common datasets. The individual datasets will expand over time. Using the SAS libname, is there a way to do it without having to itemize each dataset name in the SET statement?

Howles · ‎04-02-2012

I would add: "and uneditable". Linlin wrote: Thank you all! I think SAS should make a post undeletable once there are replies. - Linlin

Howles · ‎04-02-2012

Some clarification ... Macro variables and SAS data variables are altogether different things. Examples of automatic variables: _N_ and _ERROR_ in the DATA step, _TYPE_ and _FREQ_ in PROC SUMMARY/MEANS. The items which S. listed are not automatic variables. Rather, they are shorthand notations which expand to be lists of zero or more variables (which are generally not automatic variables). In fact, _NUMERIC_ excludes automatic variables when it is expanded. sambasiva.ravuri@tcs.com wrote: Thanks for quick response Ballardw. I got some of Automatic Variables info and am just sharing for the same. _ALL_ Lists the values of all macro variables.specifies all variables that are currently defined in the current DATA step. _AUTOMATIC_ Lists the values of all automatic macro variables _USER_ Lists the values of all user-defined macro variables _NUMERIC_ specifies all numeric variables that are already defined in the current DATA step. _CHARACTER_ specifies all character variables that are currently defined in the current DATA step. Regards, S Ravuri.

Online Status	Offline
Date Last Visited	‎09-01-2015 07:11 AM

Re: I'm trying to convert SAS code into SQL.

Re: Update existing and insert new record to sas dataset.

Re: invert a sequence column in a dataset

Re: Need help working with the dates

Re: Can PROC SQL AVG function be completed within different subsets in...

Re: difference between merge set and update

Re: Numeric and Character Observations under a Variable

Re: Can we use changing dates in proc format?

Re: Picking matched controls

Re: Date informat MMDDYY10. incorrect?

Re: how to insert a new colomn into dataset use proc sql?

Re: Use RETAIN to re-order variables

date diff SQL to Sas

UPCASE ALL VARIABLES IN DATASET

Re: converting numbers to time format?

Re: how to insert a new colomn into dataset use proc sql?

Re: Summary stats

Re: PROC SQL - same column names problem - SAS tries to be too smart

Re: question about using rand function

Re: how to rearrange the column position in sql proceure

Re: merge 1 row table to another column table

Re: Proc SQL code behavior difference when moved from SAS 9.1(32 bit) ...

Re: When I delete a post, are all the posts following my post also del...

Re: Mainframe SAS

Re: lag

how to write an accumulation statement so that all the obs in the tran...

Re: Summing only negative values of a certain variable

Is there a way to read all SAS datasets in a directory into one datase...

When I delete a post, are all the posts following my post also deleted...

List of Autiomatic variables in SAS ?