Date:         Tue, 4 Mar 2003 10:35:01 -0800
Reply-To:     "Huang, Ya" <yhuang@AMYLIN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Huang, Ya" <yhuang@AMYLIN.COM>
Subject:      Re: detect dates for an array
Comments: To: JP <jplecruguel@HOTMAIL.COM>
Content-Type: text/plain; charset="iso-8859-1"

JP,

The following code use excel file for testing (I'm
not very familiar with access), the first proc import
step read in a xls file, which has several variables,
among which two are datatime var. A proc print followed
shows that data set indeed has two datatime var. Third step
is a sql step. It first reads the metadata from
dictionary.columns, and create two var list, one is
non-datetime type var (based on its format), another is
datetime type var, a twist is that the datetime var
list actually add datapart function and date9. format
to each of the vars. A final step is use sql to recreate
data set junk.

PROC IMPORT OUT=junk
DATAFILE='h:\sas-l\sasl04mar03.xls'
DBMS=EXCEL2000 REPLACE;
GETNAMES=yes;
RUN;

options nocenter;
proc print;

proc sql noprint;
select name into :nondnm separated by ','
from dictionary.columns
where libname='WORK' and memname='JUNK' and format ne 'DATETIME20.'
;
select 'datepart('||compress(name)||') as '||compress(name)||' format=date9.'
 into :datenm separated by ','
from dictionary.columns
where libname='WORK' and memname='JUNK' and format = 'DATETIME20.'
;
create table junk as
select &nondnm, &datenm
from junk
;

proc print data=junk;
run;
-----------
Obs    a                           d1                      d2     c

 1     as          23MAR2003:00:00:00      15APR2001:00:00:00    123
 2     asdasd      24MAR2003:00:00:00      16APR2002:00:00:00    123

Obs    a          c            d1           d2

 1     as        123    23MAR2003    15APR2001
 2     asdasd    123    24MAR2003    16APR2002


The best part of this approach is the final data set has the same
var names as old data set. Note the order is changed though.

Hope this helps.

Ya Huang


-----Original Message-----
From: JP [mailto:jplecruguel@HOTMAIL.COM]
Sent: Tuesday, March 04, 2003 9:15 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: detect dates for an array


Hi all,


I have some access tables to import in SAS on a frequent basis.

I use the Import proc.

PROC IMPORT OUT= IMPORTED.Sect1
            DATATABLE= "Sect1"
            DBMS=ACCESS97 REPLACE;
     DATABASE="C:\unmet needs data All subjects.mdb";
RUN;

My problem is with dates variables. By default, they are set in the
DATETIME format (date + hours...). I want them to be casual SAS date
(day).
I know I cannot specify format or informat in Proc Import.

I use newdate=DATEPART(oldate); to convert my dates.

But how can I do it in an automatic datastep?
I could use an array, but I would like to avoid to specify all the
date variable. Is there any _numeric_ or _character_ thing applicable
to date variables?

The other option would be to write a macro, reading the output from
proc contents to get the list of those with a datetime format.

JP