About buckeye

buckeye · ‎07-19-2011

It appears it is not required in SAS proc sql as illustrated in Tom's solution below: select group , category , count from have group by group Aggregate functions, such as min(), max(), sum(), are not required for, in this case, category and count. Hope this clarifies.

buckeye · ‎07-19-2011

It is good to know that aggregate function is not needed when grouping. This is different from SQL statements in languages rather than SAS.

buckeye · ‎07-15-2011

Thanks for the observation, Art. It is consistent with my experience that datastep is about always quicker than proc sql, especially when dealing with larger number of records in a dataset, with multiple tables.

buckeye · ‎07-15-2011

saspert, this would give what you wanted. data cat; input category $ count; cards; Asian 12 American 3 European 25 Unknown 31 All 71 ; run; proc sql; create table tmp as select count(*) as totcnt,sum(case when count>10 then 1 else 0 end) as catcnt from cat where category not in ('Unknown','All'); create table result as select * from cat where category in ('Unknown','All') or (select totcnt from tmp) = (select catcnt from tmp); quit; proc print data = tmp; proc print data = result; run;

buckeye · ‎07-05-2011

It works beautifully. Thank you very much mojerry2!

buckeye · ‎06-30-2011

Hi all, I need to conditionally connect to different SQL Server in SAS programs based on the server the SAS porgram runs. How to get the server identifying information? Thanks a lot in advance!

buckeye · ‎06-30-2011

Thank you very much, chang_y_chung. The link provides very helpful infomation. It turns out that we don't currently have license (Integration Technologies) to access SAS data files from outside SAS. But we do currently have license (SAS/Access) to access SQL Server from a SAS program. I now have an issue dealing with SQL Server datetime type field. The column as SAS sees is (InvoiceDate is a datetime type defined in SQL Server): InvoiceDate Num 8 DATETIME22.3 DATETIME22.3 InvoiceDate The field in SAS data file is: INVCDATE Num 8 MMDDYY10. INVOICE DATE (SAS) But when I tried to insert the record into SQL Server from SAS data file, I got data type mismatch error or inserted incorrectly (showing 1960-01-01 xxxxxx). Does anybody have a solution to this? Thanks in advance!

buckeye · ‎06-27-2011

Hi all, How to set up an enviroment in MS Visual Studio so we can run SQL command from MS Visual Studio against SAS datasets? Thanks very much in advance!

buckeye · ‎06-16-2011

Editor's Note: The solution provided by buckeye will allow you to total up SALES while capturing the earliest DATE. Because a SAS date is a numeric value, the MIN function can be used to capture the earliest date. The DATA step version of buckeye's PROC SQL code is show below as well. Please note that in order for the DATA step version to work, the data set will have to be sorted by ID and DATE. PROC SQL; CREATE TABLE mySum AS SELECT ID,MIN(date) AS Earliest, SUM(sales) AS SalesTot FROM salesrecord GROUP BY ID; QUIT; /*DATA Step version of the above code*/ data a; input id date : date9. sales; datalines; 1 01Jan11 12 1 02Jan11 20 2 12Jan11 34 2 23Feb11 21 ; data b; set a; by id; retain earliest; if first.id then do; earliest=date; sales_sum=0; end; sales_sum+sales; if last.id then output; drop sales date; run; proc print; format earliest date9.; run;

buckeye · ‎05-26-2011

That is exactly what I need. Thanks a lot.

buckeye · ‎05-26-2011

Hi all, With Number specified in proc sql as an option shown below, but I couldn't get it in the subsequent data file mytable. Is the ROW just for the printing purpose and we can not use it as a field or column? Thanks in advance. data tbl; input first $1. second third fourth ; datalines ; a 21 22 23 s 31 32 33 d 41 42 43 f 51 52 53 ; run ; proc sql number ; create table mytbl as select * from tbl; /* select statements */ select * from mytbl; select 'r' || row as myrownumber, * from mytbl; quit;

buckeye · ‎04-21-2011

this should work: data try1; input rec FIRST_CZ NEXT_CZ; datalines ; 1 . . 2 . . 3 3 5 4 . . 5 . . 6 . . 7 . . 8 . . ; run ; data try2 (DROP = LAG:); set try1 ; LAGfirst_cz=lag(FIRST_CZ) ; LAGnext_cz=lag(NEXT_CZ); LAG2first_cz=lag2(FIRST_CZ) ; LAG2next_cz=lag2(NEXT_CZ); if (LAGFIRST_CZ>0 and FIRST_CZ=.) then do; first_cz=lagFIRST_CZ ; next_cz=lagNEXT_CZ; end; else if (LAGFIRST_CZ=. and LAG2FIRST_CZ>0 and FIRST_CZ=.) then do; first_cz=lag2FIRST_CZ ; next_cz=lag2NEXT_CZ; end; run; PROC SQL; SELECT * FROM TRY2; QUIT; run;

buckeye · ‎04-21-2011

Following Ksharp's tip, it appears this should work: LIBNAME DAT 'my:\mylib'; PROC SQL ; SELECT DISTINCT 'PROC CONTENTS DATA =DAT.' || COMPRESS(MEMNAME,' ') || ';' INTO :STR SEPARATED BY ' ' FROM DICTIONARY.COLUMNS WHERE MEMTYPE='DATA' ; QUIT; &STR; RUN; You may want to change the where clause as this: WHERE MEMTYPE='DATA' AND MEMNAME LIKE 'mytbl%' to list all tables with name begining 'mytbl'. Hope this helps.

buckeye · ‎04-20-2011

Your thinking is naively deep and in a wrong direction. No one's time is more valuable professionally than other's in this forum. We come here to seek solutions or "shortcuts" and enjoy the programming in SAS. Please don't think any other ways, otherwise please just have a laugh and don't reply.

buckeye · ‎04-20-2011

Yes, I did. It works. You're my hero. I now have a much better understanding of how macro works. Thanks a lot.

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

Conditional Data Step/Proc Sql

Conditional Data Step/Proc Sql

Conditional Data Step/Proc Sql

Conditional Data Step/Proc Sql

Executing SQL from Visual Studio against SAS datasets

How to programmatically know which server an SAS program is running?

Executing SQL from Visual Studio against SAS datasets

Executing SQL from Visual Studio against SAS datasets

Re: Max/Min date function on a variable

Re: Where is ROW in proc sql?

Conditional Data Step/Proc Sql

Conditional Data Step/Proc Sql

Conditional Data Step/Proc Sql

Conditional Data Step/Proc Sql

Conditional Data Step/Proc Sql

Executing SQL from Visual Studio against SAS datasets

How to programmatically know which server an SAS program is running?

Executing SQL from Visual Studio against SAS datasets

Executing SQL from Visual Studio against SAS datasets

Re: Max/Min date function on a variable

Re: Where is ROW in proc sql?

Where is ROW in proc sql?

Re: Help, why this won't work?

Re: PROC CONTENTS for Database Tables ONLY

Re: Return values, sort of

Re: Return values, sort of