About Keith

Keith · ‎07-10-2012

Here's a simple way. data have; input var1 var2 $ var3 $ var4a var4b var5 $ var6; cards; 1 a y 1515 2323 orange 500 2 b n 2626 3434 blue 600 3 c y 3737 4545 green 700 4 d n 4848 5656 red 800 ; run; data want; set have (drop=var4b rename=(var4a=var4)) have (keep=var4b rename=(var4b=var4)); run;

Keith · ‎07-10-2012

Another option is to create an informat which is then used to convert the data. The following code creates informats for all months from Jan 2000 - Dec 2012, obviously you can easily increase this range in the loop. proc format; picture dt_pic low-high='%B %Y' (datatype=date); run; data dt_infmt; retain fmtname 'dt_in' type 'J'; do i=0 to 155; label=intnx('month','01jan2000'd,i); start=strip(put(label,dt_pic15.)); output; end; run; proc format cntlin=dt_infmt; run; data want; set have; new_date=input(date,dt_in.); format new_date date9.; run;

Keith · ‎07-10-2012

If you are updating / appending a small number of records in your large dataset then using MODIFY may be the most efficient technique. And if you haven't done so already, I would recommend creating an index on COL1 in your master dataset. Below are 2 examples of updating the master dataset (indexed) using MODIFY, the 2nd example is likely to be fastest if your transaction dataset is small. There's a good paper on using MODIFY at http://www2.sas.com/proceedings/sugi31/264-31.pdf data have (index=(col1)); input (id col1 col2) ($); cards; 5 id2 desc3 1 id1 desc1 ; run; data new; input (id col1 col2) ($); cards; 4 id3 desc4 3 id1 desc2 ; run; /* example 1 */ data have ; modify have new (rename=(id=id_new col2=col2_new)); by col1; select (_iorc_); when (%sysrc(_sok)) do; col2=col2_new; replace; id=id_new; output; end; when (%sysrc(_dsenmr)) do; id=id_new; col2=col2_new; output; _error_=0; end; otherwise end; run ; /* example 2 */ data have; set new (rename=(id=id_new col2=col2_new)); modify have key=col1; select (_iorc_); when (%sysrc(_sok)) do; col2=col2_new; replace; id=id_new; output; end; when (%sysrc(_dsenom)) do; id=id_new; col2=col2_new; output; _error_=0; end; otherwise end; run; Message was edited by: Keith Timms Sorry, just realised that this method only works properly where the values for COL1 are unique in the master dataset. I'll have a look to see if I can get it working where duplicate values exist.

Keith · ‎07-06-2012

Another option is Proc Summary. The 2nd example is based on the maximum value being returned when an ID statement is used. proc summary data=have nway; class case_number; var case_value; output out=want (drop=_:) max=; run; or proc summary data=have nway; class case_number; id case_value; output out=want (drop=_:); run;

Keith · ‎07-06-2012

You have a couple of errors in your code. You need an Output statement within the loop, otherwise it will only output the last iteration (the previous ones are overwritten each time) The %end should come before the Run statement You can also use the CATS function to concatenate the data and remove unwanted blanks, which will save using COMPRESS separately. There's some slight confusion as to what you want to show in your output, should it be id1, id2 etc or d1, d2 etc ? To achieve the former, put quotes around id in the concatenate function, otherwise leave it as is and it will take the value of the column id. Here is the code I think you want, I've added a length statement to prevent the default $200. You don't really need the macro either, just use a normal loop within the datastep. %let a=5; data l2; length id $6; set l; do i= 1 to &a; t=cats(id,i); output; end; run; proc print data=l2; run; Message was edited by: Keith Timms Obviously the length statement should against column t, not id.

Keith · ‎07-06-2012

I came across this problem fairly recently with Oracle tables (as well as column names >32 characters). Having done lots of research into it, the only solution we could find that worked (apart from renaming the tables) was to create views with names <32 characters. Fortunately I was dealing with internal data, so it wasn't too much trouble to get the new views created. With long column names, SAS just truncates them so they are still visible, but it can't do this with table names. Sorry I couldn't give you a better solution.

Keith · ‎07-04-2012

Apologies, I was thinking the 'nodupkey' was in place, not 'nodup'. I have another method of achieving your output dataset, using PROC SUMMARY. This works fine for datasets with not many variables and rows, but I wouldn't recommend using it for large datasets. You won't need to presort the data this way. proc summary data=best nway; class _all_; output out=ex2 (drop=_:); run;

Keith · ‎07-04-2012

Not easy with an unsorted dataset. You could store the concatenated values of arm and patient in a temporary array which adds 1 for each row read in, then compare the value of the current row with the previous array values. If it matches then delete the row. This means the maximum array size will be the number of rows in the dataset, I imagine this could cause memory issues for large datasets. The main question is why you want to do this?

Keith · ‎07-04-2012

You could simplify the compress function to keep just the letters A-Z,a-z, rather than remove non-letters. modified = compress(name,,'ka');

Keith · ‎07-03-2012

I'm not sure if you can output the odds, however you can output the estimated probability (from which you could easily calculate the odds) proc logistic data=sashelp.class; output out=test predicted=prob; model sex= age height weight; run;

Keith · ‎07-03-2012

Yes, there is a SELECTION= option in the MODEL statement within PROC LOGISTIC. The methods available are BACKWARD, FORWARD, STEPWISE and SCORE. Check out the SAS documentation below. http://support.sas.com/documentation/cdl/en/statug/63347/HTML/default/viewer.htm#statug_logistic_sect016.htm

Keith · ‎06-27-2012

To start with, you need to restructure your dataset as it is very difficult to achieve your goal with the present structure. You should have 2 columns, 'Prep' and 'Strength', containing the relevant values, instead of the QSN and Value columns. It will then be much easier to summarise. The code below gives you output identical to yours, however I suggest if you have more than the 3 value types in your example then you wrap the calculation part inside a macro. data have1; input ID QSN $ VALUE $; cards; 1 Prep Zov 1 Prep Pan 1 Prep Dol 1 Strength 50 1 Strength 50 1 Strength 100 2 Prep Zov 2 Prep Zov 2 Prep Zov 2 Strength 55 2 Strength 50 2 Strength 60 run; /* restructure dataset */ data temp1; set have (where=(qsn='Prep')); rename value=prep; drop qsn; run; data temp2; set have (where=(qsn='Strength')); strength=input(value,best12.); drop qsn value; run; data have2; merge temp1 temp2; by id; run; /* summarise data */ data want; set have2; by id; if first.id then call missing(zov,zov_strength,pan,pan_strength,dol,dol_strength); retain zov_strength pan_strength dol_strength; zov+prep='Zov'; if prep='Zov' and strength>zov_strength then zov_strength=strength; pan+prep='Pan'; if prep='Pan' and strength>pan_strength then pan_strength=strength; dol+prep='Dol'; if prep='Dol' and strength>dol_strength then dol_strength=strength; if last.id then output; drop prep strength; run;

Keith · ‎06-14-2012

Just do a proc freq and merge the output table back on to the original. I've used the coalesce function in the remerge query to put a 0 for any months where the codes 5523,1213 don't appear. proc freq data=have (where=(codes in (5523,1213)) noprint; table monthyear / out=want; run; proc sql; create table have as select a.*, coalesce(b.count,0) as distcd from have as a left join want as b on a.monthyear=b.monthyear; quit;

Keith · ‎05-31-2012

I believe the problem arises because SAS treats 'Other' as the final category of the format, irrespective of where it is placed. Therefore low-high will be processed beforehand, resulting in no possible values for 'Other'. You therefore need a workaround such as that suggested by @Ksharp.

Keith · ‎05-30-2012

Just put LOW - HIGH = TOTAL to include all values.

Online Status	Offline
Date Last Visited	‎09-11-2015 12:18 PM

Re: Regarding If and then statement

Re: Merging 3 datasets

Re: Regading if and then

Re: Retaining Values in a Do Loop

Re: Using column wildcard (:) with retain statement in a datastep

Re: Select observations that meet a criteria

Re: Select observations that meet a criteria

Re: How to read Input data

Re: Output next,or next and next,or next and next and next,hahaha

Re: Re-Arrange Variables

Re: Find the missing values in the year

Re: Increment row based on change in record

Re: Dealing with 32 character restrictions

Re: calculating averages on floating period

Need help in matching the obs from dataset to excel

Re: How to change the name and format of PROC IMPORT variables

Re: Stacking a single column

Re: convert date format

Re: sql

Re: How to get one subset like this?

Re: Increment based on macro

Re: Dealing with 32 character restrictions

Re: Can we get the same ex2 dataset in data step also? If so please gi...

Re: Can we get the same ex2 dataset in data step also? If so please gi...

Re: help in extracting string

Re: How to do variable selection for logistic regression?

Re: How to do variable selection for logistic regression?

Re: Transforming dataset. Using values to create new variable names an...

Re: Simple Count

Re: format multilabel all values

Re: format multilabel all values