darrylovia Tracker

Re: PROC SQL - Insert date on Oracle table

darrylovia — Thu, 01 Nov 2012 14:27:24 GMT

A few things.

the value clause only accepts values not expressions/functions such as datetime. To get what you want I would create temporary SAS data set then insert that dataset into your oracle table. See my example below

data one;

det=1;

mydate=datetime();

format mydate datetime.;

run;

proc sql;

insert into myora.test1 (det,mydate)

select det,mydate

from one;

quit;

Re: Can we use proc univariate for getting a clue on group cut off lines

darrylovia — Fri, 13 Jul 2012 17:24:52 GMT

You could also use the HISTOGRAM option after var income / histogram; to see what SAS comes up with.

Re: How to quickly identify variables that distinguish records uniquely

darrylovia — Thu, 17 May 2012 14:19:27 GMT

A quick question before I try to answer.

If your source data is in a RDMS like Oracle, DB2, Teradata, SQL server, you can query the system tables and find the index on the tables which are unique. If not any of the answers above will work.

How do I get a multi-level ODBC path to work?

darrylovia — Fri, 04 Nov 2011 18:33:25 GMT

did you try the owner= option

for SQL Server it is usually dbo

From Base SAS to Enterprise Guide

darrylovia — Thu, 28 Jul 2011 15:00:39 GMT

I would put in a help ticket. It looks like the user (you) has not been put into the system via sas management console.

Re: Do loop or array?

darrylovia — Mon, 13 Jun 2011 20:00:27 GMT

Generally with an array you use some type of do loop. See my example below:

data b;
set a;

array m(12);
zero=0;
do i=1 to dim(m);
zero=zero+(m ne 0);
end;
run;

Re: Way to pick the latest file

darrylovia — Thu, 02 Jun 2011 16:57:49 GMT

you can also check DINFO to get all the items in a directory

Re: Where is ROW in proc sql?

darrylovia — Thu, 26 May 2011 20:01:38 GMT

I've used the MONOTINC function with no ill effects for one table processing. I believe when joins occur the MONOTONIC function may fail. Sounds like a fun experiment!

Re: simple question

darrylovia — Wed, 25 May 2011 19:29:11 GMT

i did notice in your code that the LIBNAME names uses OLEBD to connect to SQL Server and the SQL Pass-through uses ODBC. Try using the same connection method and see what happens.

D

Re: Function Multiply

darrylovia — Mon, 23 May 2011 20:23:41 GMT

Unfortunately not.
I would love to have a PRODUCT function and a SUMPRODUCT function, but in the mean time you'll have to use an ARRAY to calculated it.

Re: How to move file folder-to-folder

darrylovia — Thu, 19 May 2011 15:22:15 GMT

Hello

I work exclusively in SAS Enterprise Guide and have had the same problem that you have. I solved mine by directly accessing the Windows DLL that contains the copy/move function by using the SASCBTL attribute table with the MODULE family of call routines and functions. Below is a link to a paper that I wrote for SESUG 2010 that describes how to do this.

paper
http://analytics.ncsu.edu/sesug/2010/CC06.Putnam.pdf

SAS DOC
http://support.sas.com/documentation/cdl/en/hostwin/63285/HTML/default/viewer.htm#accdll.htm

Enjoy
D

Re: Convert full date to year and week of the year?

darrylovia — Tue, 17 May 2011 17:58:15 GMT

Just convert the date to a character like below

data two;
set one;
format myweek weeku6.;
myweek=raw_date;
cat_week=put(raw_date,weeku6.);
run;

Re: Convert full date to year and week of the year?

darrylovia — Tue, 17 May 2011 14:50:54 GMT

SAS has a number of date functions and formats that you could use to solve your problem. Try the WEEK function or WEEKU./WEEKW. formats

Try looking at the documentation
http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a003154994.htm
http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a002604217.htm

below is a quite example for week begining with Sunday

data one;
format raw_date yymmddp10.;
input raw_date yymmdd10.;
datalines;
2009.01.05
2009.01.06
2010.01.05
2011.01.05
;
run;

data two;
set one;
format myweek weeku6.;
myweek=raw_date;
run;

Re: How to check if new record is added in a sas dataset

darrylovia — Tue, 17 May 2011 11:27:52 GMT

It all depends on how the new record is inserted into your master table and how big your table is. There are many was to accomplish this functionallity.

I would use any of the 4 methods below:
1) I would create a timestamp and the most recent record would be the max timestamp, additional you can create a new record flag from the transaction set.

2) create a new record flag from the incoming data set
See below for a quick code snippet.

3) create an audit data set

4) create a unique sequential id for your master file and store the max value in another table. All inserted record would be incremented by 1 then any ID greater than the max id would be new.

data master_table;
format timestamp datetime21.2;
ID=1;value='HELLO';timestamp=datetime();output;
ID=2;value='WORLD';timestamp=datetime();output;
run;
proc sql;
create table max_id as
select max(id) as max_id
from master_table;
select max_id
into :max_id
from max_id;
quit;

data transaction_table;
value='TEST ';
run;

data master_table;
set master_table (in=inMaster)
transaction_table (in=inTransaction)
;
* create timestamp to find newest record;
* create 1/0 indicator for newest record;
* increment id by 1;
if inTransaction then do;
counter+1;
timestamp=datetime();
new=(inTransaction);
id=&max_id+counter;
end;
run;

Re: RSUBMIT batches unresponsive

darrylovia — Mon, 16 May 2011 21:11:42 GMT

i don't think the CONNECTWAIT option is causing your problems. Since the log snippet isn't producing an ERROR maybe your data doesn't have any rows which may be unexpected.

Is there any subsetting based on dates or anything like that when the data sets like WORK.FIVE is being produces?

Re: Random sample until

darrylovia — Mon, 16 May 2011 19:18:09 GMT

for some reason my post was truncated.

here is the completed code

data stocks;
set sashelp.stocks;
weight=ranuni(8)*.05;
rand=ranuni(789);
run;

proc sort data=stocks;
by rand;
run;

data portfolio;
set stocks;
retain one 0;

one+weight;
if one le 1;
run;

Regards

Re: Random sample until

darrylovia — Mon, 16 May 2011 19:13:58 GMT

It seems that you're is approach is good and that you are stuck on how to increment to weight up to 1. SAS has a RETAIN feature that retains the value from one record to another. See my below code using an example;

data stocks;
set sashelp.stocks;
weight=ranuni(8)*.05;
rand=ranuni(789);
run;

proc sort data=stocks;
by rand;
run;

data portfolio;
set stocks;
retain one 0;

one+weight;
if one<=1;
run;

Regards

Re: datalines eof option

darrylovia — Thu, 12 May 2011 11:43:43 GMT

Since you only want the last record you can use a subsetting if

data vac_used;
retain vac_used 0;
input date1 :ddmmyy10. date2 :ddmmyy10. holidays :5.;
days1=date2-date1+1-holidays;
vac_used=vac_used+days1;
format date1 date2 date9.;

if _n_=4;

put _all_;
datalines;
18.05.2009 22.05.2009 0
24.08.2009 11.09.2009 0
13.10.2010 29.10.2010 0
27.12.2010 31.12.2010 0
;
run;

Re: Create a matrix using a SAS dataset with row and column information

darrylovia — Wed, 11 May 2011 15:50:10 GMT

I have a general approach that uses BASE SAS and not PROC IML but this should get what you are looking for.

** Data in a listing structure;
data one;
input row column element;
datalines;
1 1 0.5
1 3 0.2
2 1 0.4
2 2 0.6
3 2 0.7
3 3 0.1
;
run;

** get maximum dimension;
proc sql;
select max(column)
into :c
from one;
quit;
%let c=&c;

%put c=&c;

proc sort data=one;
by row;
run;
** Convert data from listing to matrix structure;
data matrix(keep=x:);
set one;

array x(&c);

do until (last.row);
set one end=eof;
by row;
x(column)=element;
end;

do i=1 to dim(x);
if missing(x(i)) then x(i)=0;
end;
run;

Re: ERROR: Function SUM requires a numeric expression as argument 1

darrylovia — Thu, 21 Apr 2011 12:50:43 GMT

> Why does your sum statement have two 'sum' functions?
> I can't test it at the moment, but would suggest
> trying it as sum(normal,etc., etc.).
>
> Art
> -----------
> > Hi
> >
> > I am trying to sum using proc sql but I get the
> > following on the log window
> >
> > proc sql;
> > 519 create table limbs3 as
> > 520 select
> > 521 id,
> > 522 sum(sum(normal, alo2, call3, red4, wound5,
> > callswe6,
> > 523 swound7, sswe8, swound_sswe9)) as
> score
> > 524 FROM limbs_sum;
> > ERROR: Function SUM requires a numeric expression
> as
> > argument 1.
> > ERROR: Function SUM requires a numeric expression
> as
> > argument 2.
> > ERROR: Function SUM requires a numeric expression
> as
> > argument 3.
> > ERROR: Function SUM requires a numeric expression
> as
> > argument 4.
> > ERROR: Function SUM requires a numeric expression
> as
> > argument 5.
> > ERROR: Function SUM requires a numeric expression
> as
> > argument 6.
> > ERROR: Function SUM requires a numeric expression
> as
> > argument 7.
> > ERROR: Function SUM requires a numeric expression
> as
> > argument 8.
> > ERROR: Function SUM requires a numeric expression
> as
> > argument 9.
> > ERROR: The SUM summary function requires a numeric
> > argument.
> > 525 group by id;
> > 526 quit;
> >
> >
> > what am I doing wrong?
> >
> > Thanks for your help

I think you put a semi-colon after your FROM CLAUSE and that needs to be removed.
The syntax works for me by using the below code
proc sql;
create table table1 as
select region
,sum(sum(sales,returns)) as gross_sales
from sashelp.shoes
group by region;
quit;

yet if I use a char data type i get your message. see below

16 proc sql;
17 create table table3 as
18 select region
19 ,sum(sum(product,returns)) as gross_sales
20 from sashelp.shoes
21 group by region;
ERROR: Function SUM requires a numeric expression as argument 1.
ERROR: The SUM summary function requires a numeric argument.

Also I would check the data types of your table

proc sql;
describe table limbs_sum;
quit;

to double check

Add to Art297. the sum by itself just adds the columns together, the double sum then aggregates the summed columns .

D