About Howles

Howles · ‎08-23-2011

There's a disconnect here. The code uses a character informat ($10.) but the narrative describes behavior of a date informat (ddmmyy10.). So consider instead this example: options yearcutoff=5050 ; data _null_ ;; input vtext $10. @1 vdate ddmmyy10. ; put vtext= vdate= date9. / ; cards ; 01.01.0000 01.01.0001 31.12.0049 01.01.0050 31.12.0099 01.01.0100 31.12.1581 01.01.1582 ; Results: vtext=01.01.0000 vdate=01JAN5100 vtext=01.01.0001 vdate=01JAN5101 vtext=31.12.0049 vdate=31DEC5149 vtext=01.01.0050 vdate=01JAN5050 vtext=31.12.0099 vdate=31DEC5099 vtext=01.01.0100 vdate=. vtext=31.12.1581 vdate=. vtext=01.01.1582 vdate=01JAN1582 So years from 0001 to 0099, which should be rejected as out-of-range, are instead processed using the YEARCUTOFF option, as if the two leading zeroes weren't there. Documentation says that YEARCUTOFF only applies to 2-digit years. Here's a workaround. Define an informat like proc format ; invalue dmy111_ '01.01.0001' = . other = [ddmmyy10.] ; run ; and use it to process the date fields. christyh wrote: Hi there, I'm getting some raw data that has the following dates in the file: 01.01.0001 but when I read it in, it comes in as 01.01.2001. Here is an example: DATA temp; input polno $ prdtstrt $10. prdtend $10.; cards; AA1 01.01.000131.12.9999 AA2 01.01.000131.12.9999 AA3 01.01.200115.10.2009 AA4 01.11.201001.11.2011 AA5 01.03.201101.03.2012 AA6 01.05.201101.05.2011 ; run; I need to be able to distinguish 0001 from 2001. I don't mind if all these values are set to missing, just don't want them in the dataset as 2001. Thanks, Christy

Howles · ‎08-18-2011

If the DATEPART extractions are needed for other purposes, go ahead with them. However, the day difference can be calculated directly from the given datetimes: daysdiff=intck('dtday', startdate, enddate) ; robby_beum wrote: Try this: data P01.output_ds; format start end date9.; set P01.input_ds; start=datepart(startdate); end=datepart(enddate); daysdiff=intck('day', start, end); run;

Howles · ‎08-16-2011

Are ties possible (2 or more tests in the earliest year for an ID)? If so, what should materialize in FIRSTEXAM?

Howles · ‎08-13-2011

You can have it both ways. Use the TO/BY spec along with an UNTIL condtion. data test; do count=1 TO 6 UNTIL (count EQ 6); end; output; run; art297 wrote: In the statement you used, x=1 to 6, 6 only represented the stop condition. I.e., the loop stopped when x became greater than 6 and, since you didn't specify an increment (i.e., a by value), it incremented each loop by 1. If you don't use the from,to form of the do statement, you can get it so that it won't increment beyond the specified values. E.g., data test; do count=1,2,3,4,5,6; end; output; run;

Howles · ‎08-05-2011

If you don't want to reshape the data, a roll-your-own approach with a Double DoW can do it. Something like data have; input var1-var3; cards; 1 2 3 4 5 6 7 8 9 ; data _null_ ; do until (lastobs) ; set have end=lastobs ; n_v + n(of var:) ; sum_v + sum(of var:) ; end ; lastobs = 0 ; do until (lastobs) ; set have end=lastobs ; array vv var: ; do j = 1 to dim(vv) ; sumsq + ( vv - (sum_v / n_v) )**2 ; end ; end ; std = sqrt( sumsq/(n_v-1) ) ; put std= ; run ; richard_hu2003 wrote: Hi folks, I need to compute Standard Deviation of ALL Observations of MULTIPLE Variables. PROC MEANS can compute std of EACH variable. STD function can compute std of EACH observation. Is there a way to compute std of all observations (data points) of multiple variables? For example, I have 3 variables, var1, var2, var3 and 3 observations. var1 var2 var3 1 2 3 4 5 6 7 8 9 PROC MEANS can compute std of EACH variable and return std1 of (1, 4, 7), std2 of (2, 5, 8), std3 (3, 6, 9). STD function in data step can compute std of EACH observation and return std_1_ of (1,2,3), std_2_ of (4, 5, 6) and std_2_ of (7, 8, 9). But I want to get ONE single standard deviation of all 12 data points, std_all of (1,2,3,4,5,6,7,8,9). Thanks.

Howles · ‎08-05-2011

I don't think it's documented, at least at http://support.sas.com/documentation//cdl/en/leforinforref/63324/HTML/default/viewer.htm#n0mv0kngshd5sun1j5d38sbttwz1.htm My theory is that once SAS has detected three delimiters, it knows enough to extract the MM, DD, and YY subfields and stops looking. My test: data _null_ ; do x = "8/7/09 1:23:45" , "8/7/09#1:23:45" , "8/7/09J1:23:45" , "8/7/09JJJ11111" , "8/7/09JJJJ1111" ; date = input(x,mmddyy10.) ; format date date9. ; putlog x= date= ; end ; _error_ = 0 ; run ; Result: x=8/7/09 1:23:45 date=07AUG2009 x=8/7/09#1:23:45 date=07AUG2009 x=8/7/09J1:23:45 date=07AUG2009 x=8/7/09JJJ11111 date=07AUG2009 NOTE: Invalid argument to function INPUT at line 669 column 11. x=8/7/09JJJJ1111 date=. Seems that letters, not just special characters, work as delimiters. But there is evidence that SAS somehow cares about whatever comes after that third delimiter. data_null_; wrote: I thought about that but didn't try it. Is the algorithm used by the informat documented? Howles wrote: It seems to be even simpler: data _null_ ; x = "8/7/09 1:23:45" ; date = input(x,mmddyy10.) ; format date date9. ; putlog _all_ ; run ; data_null_; wrote: There are easier methods... 322 data _null_; 323 x = "8/7/2009 0:00:00"; 324 dt = input(x,mdyampm.); 325 put dt=datetime.; 326 date = datepart(dt); 327 put date=date9.; 328 run; dt=07AUG09:00:00:00 date=07AUG2009

Howles · ‎08-02-2011

It seems to be even simpler: data _null_ ; x = "8/7/09 1:23:45" ; date = input(x,mmddyy10.) ; format date date9. ; putlog _all_ ; run ; data_null_; wrote: There are easier methods... 322 data _null_; 323 x = "8/7/2009 0:00:00"; 324 dt = input(x,mdyampm.); 325 put dt=datetime.; 326 date = datepart(dt); 327 put date=date9.; 328 run; dt=07AUG09:00:00:00 date=07AUG2009

Howles · ‎08-01-2011

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

Howles · ‎08-01-2011

I nominate PROC TABULATE. proc tabulate data=x ; class subsys ; var CAP ALLOC AVAL ; table subsys all='' , (CAP ALLOC AVAL)*mean='' ALLOC='PERCNTUSED'*pctsum<CAP>=''*f=10.1 ; run ; MikeZdeb wrote: Hi ... there was a PROC SQL suggestion, so ... You use PROC SQL just like PROC PRINT, just don't create a table. Since one of the values of AVAL ends in a zero, a format (6.2) is used to assure two decimal places in the table (the width also accomodates the SUM). Otherwise ... select * , 100*alloc/cap as PERCENT format=4.1 from x would suffice. PROC SQL uses the variable names as entered, so they are all in CAPS. data x; input SUBSYS : $6. CAP ALLOC AVAL; datalines; 2107#1 67.94 49.26 18.68 2107#2 67.94 50.93 17.01 2107#3 69.39 39.54 29.85 2107#4 67.24 24.94 42.30 ; run; proc sql; select SUBSYS, CAP, ALLOC, AVAL format=6.2, 100*alloc/cap as PERCENT format=4.1 from x union all select 'TOTAL' as subsys, sum(cap), sum(alloc) , sum(aval), 100*sum(alloc)/sum(cap) from x; quit; SUBSYS CAP ALLOC AVAL PERCENT ------------------------------------------- 2107#1 67.94 49.26 18.68 72.5 2107#2 67.94 50.93 17.01 75.0 2107#3 69.39 39.54 29.85 57.0 2107#4 67.24 24.94 42.30 37.1 TOTAL 272.51 164.67 107.84 60.4

Howles · ‎07-28-2011

APPEND is a specialized tool, and that allows a degree of optimization (block operations, etc.). The DATA step is a very flexible thing, but at a cost. It drags all of the data, an observation at a time, through the program data vector. That adds overhead. I'm pretty sure there's no re-reading. That would have to be deliberately contrived. Also: OPEN=DEFER may help in the DATA step, if the data sets meet the requirements. art297 wrote: Interestingly, yes, proc append (and/or probably using append in proc datasets) is quite a bit more efficient. I wonder why the same operation uses a different algorithm in a datastep. There shouldn't be any need to re-read each file when appending additional files, but the processing time indicates otherwise.

Howles · ‎07-28-2011

Since MATH and PHY have the same structure, just use a simple UNION. create table want as select * from math union select * from phy ; willy0625 wrote: So I am trying to combine the two tables given below by using PROC SQL. DATA Math; INPUT name $ mark; DATALINES; A 94 B 92 ; DATA Phy; INPUT name $ mark; DATALINES; B 92 C 81 ; This is the output I want: name mark A 94 B 92 C 81 But I keep getting name mark A 94 B 92 . 81 Any suggestions? Many thanks

Howles · ‎07-27-2011

Avoid the correlated subquery select * from demo where pidate BETWEEN '01jun2011'd AND '30Jun2011'd having pidate EQ max(pidate); This does employ remerging. Doc@Duke wrote: Something like this should work (untested code): SELECT * FROM <data> WHERE pidate BETWEEN '01jun2011'd AND '30Jun2011'd AND pidate=(SELECT max(pidate) FROM <data> WHERE pidate BETWEEN '01jun2011'd AND '30Jun2011'd ); A couple of notes: -- the BETWEEN operator is inclusive, so I modified your dates. -- this is a correlated subquery, so performance can be poor in large datasets (see documentation). Doc Muhlbaier Duke

Howles · ‎07-25-2011

I'm pretty sure that MONOTONIC always returns positive values, so the HAVING and GROUP BY clauses here don't do anything. DISTINCT is not a function, so the parens around NAME also have no effect. We still need the AGE column to emulate the SORT/NODUPKEY results. This create table want as select name , max(age) as age from have group by name ; but only in the special case of a single satellite column. Were there multiple satellite columns (like AGE and SEX), no go. art297 wrote: Howard, The following will work with or without ties, but uses the undocumented monotonic function: Data have; input name $ age; cards; A 20 A 20 A 21 B 22 C 23 ; proc sql noprint; create table want as select distinct(name) as onename from have group by name having min(monotonic()) gt 0 ; quit; But, I still don't see it necessarily working any faster than using proc sort.

Howles · ‎07-24-2011

Seems to me that you can omit the GROUP BY and HAVING clauses and get the same WANT; the HAVING condition can never be false. Then joining this result to the original will get you back to ... the original. This will work as long as there are no ties: create table want as select * from have group by name having age EQ max(age) ; art297 wrote: Howard, Not if one first selects just based on name (e.g.) proc sql noprint; create table want as select distinct(name) as onename from have group by name having count(*) gt 0 ; quit; and then joins the result back with the original table. However, as mentioned, I can't see it outperforming proc sort.

Howles · ‎07-24-2011

All four rows in the example are different, so DISTINCT is going to return all four. UrvishShah wrote: Hello I have the data like this Data have; input name $ age; cards; A 20 A 21 B 22 C 23 ; nw i wan to eliminate the duplicate obs like this proc sort data = have nodupkey; by name; run; nw is there any other way to do the same by using Proc Sql in order to reduce the processing time

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?

Reading in 01.01.0001 dates

Struggling with splitting up data and time

need 'min' function to return variable name and not observation values

the do loop and output statement

Standard Deviation of All Observations of Multiple Variables

Re: Converting Non-SAS Dates to SAS Dates

Converting Non-SAS Dates to SAS Dates

compare colums to create new coloum

Re: Proc Print Question

Merging Files Automatically

joing tables using PROC SQL

Re: data selection help

Urvish Shah

Urvish Shah

Urvish Shah