Prev: PROC NLP Warning
Next: Multilevel modeling in proc mixed
From: Arthur Tabachneck on 19 Nov 2009 19:17
Sophia,

I didn't take the time to see what you'd have to do regarding the macro but,
unless someone else has a better idea, I would simply import the date with
one import, the other variables with a 2nd import, transpose the two files,
then merge them together with no by statement. E.g.:

PROC IMPORT OUT= WORK.adair1
/* DATAFILE= "C:\CBC\TCH\Ellen Elias\SLOS ERG Research.xls"*/
DATAFILE= "k:art\SLOS ERG Research.xls"
DBMS=EXCEL REPLACE;
/* RANGE="&DSN.$";*/
range="a1:z1";
GETNAMES=NO;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

PROC IMPORT OUT= WORK.adair2
/* DATAFILE= "C:\CBC\TCH\Ellen Elias\SLOS ERG Research.xls"*/
DATAFILE= "k:art\SLOS ERG Research.xls"
DBMS=EXCEL REPLACE;
/* RANGE="&DSN.$";*/
range="a2:z99999";
GETNAMES=NO;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

/*PROC TRANSPOSE DATA=&DSN OUT=&DSTrans(DROP=_NAME_ _LABEL_);*/
PROC TRANSPOSE DATA=adair1 OUT=A1(DROP=_NAME_ _LABEL_);
ID F1;
IDLABEL F1;
/* VAR &Vars;*/
VAR F2 F3 F4;
RUN;

/*PROC TRANSPOSE DATA=&DSN OUT=&DSTrans(DROP=_NAME_ _LABEL_);*/
PROC TRANSPOSE DATA=adair2 OUT=A2(DROP=_NAME_ _LABEL_);
ID F1;
IDLABEL F1;
/* VAR &Vars;*/
VAR F2 F3 F4;
RUN;

data A;
merge a1 a2;
run;

HTH,
Art
---------
On Thu, 19 Nov 2009 18:07:37 -0500, Sophia Tong <sophidt(a)HOTMAIL.COM> wrote:

>Dear listers,
>
>I have enjoyed StatTranfer for converting data for many years and now need
>to deal with PROC IMPORT again. I have raw data like this:
>Date 2/15/2007 4/3/2008 6/18/2009
>Rods1_R1 106.7 101.3 64.9
>Rods1_L1 125.7 89.4 132.1
>Rods1_R2 61.0 71.5 69.5
>Rods1_L2 52.0 67.5 71.5
>
>The first column are variable names and the 2nd to 4th are the data under
>each variable.
>
>This is just for one patient, so I plan to import from Excel then
>transpose to regular data layout.
>My problem is date and number are mixed, so after tranpose those date
>became something like 1/16/2069. I formatted columns into numeric in Excel
>and the following are my programs:
>
>%MACRO READ(DSN, Vars, Id, DSTrans);
>PROC IMPORT OUT= WORK.&DSN
> DATAFILE= "C:\CBC\TCH\Ellen Elias\SLOS ERG Research.xls"
> DBMS=EXCEL REPLACE;
> RANGE="&DSN.$";
> GETNAMES=NO;
> MIXED=YES;
> SCANTEXT=YES;
> USEDATE=YES;
> SCANTIME=YES;
>RUN;
>
>PROC TRANSPOSE DATA=&DSN OUT=&DSTrans(DROP=_NAME_ _LABEL_);
> ID F1;
> IDLABEL F1;
> VAR &Vars;
>RUN;
>
>RUN;
>%MEND;
>%LET Varlst=F2 F3 F4;
>%READ(Adair, &Varlst, 1, A);
>
>Any suggestions would be greatly appreaciated.
>
>Thanks,
>Sophia
From: Joe Matise on 20 Nov 2009 01:51
If your data is exactly like that, you could do as Arthur says and import
row 1 in one dataset, rows 2+ in another, and transpose/merge.


You could also transpose it inside of excel (copy; paste special, select
'transpose'). Not every solution must be in SAS I suppose :)

If you have dates in other rows, and it's too much trouble to import them in
two separate datasets, then try importing everything as character. If you
have 9.2 you can use DBDSOPTS to get to DBSASTYPE in PROC IMPORT, otherwise
in 9.1.3 you can use DBSASTYPE directly with libname access, but I don't
think it's available from PROC IMPORT. Force everything to char [no idea
what happens to the date, but it might behave; otherwise you can manually
convert it]. If that doesn't work, you can import it as numeric [as you
have already] and convert the date on your own [subtract, hmm, 20000 or
whatever the difference is... it's about 60 years' difference; I think
excel's era is 1899 or thereabouts, while SAS's is 1/1/1960].

Finally, remember to scream at whomever gave you the data in such a moronic
form. Won't accomplish much, but will make you feel better. Unless it's
you, in which case rethink your data layouts ;)

-Joe

On Thu, Nov 19, 2009 at 5:07 PM, Sophia Tong <sophidt(a)hotmail.com> wrote:

> Dear listers,
>
> I have enjoyed StatTranfer for converting data for many years and now need
> to deal with PROC IMPORT again. I have raw data like this:
> Date 2/15/2007 4/3/2008 6/18/2009
> Rods1_R1 106.7 101.3 64.9
> Rods1_L1 125.7 89.4 132.1
> Rods1_R2 61.0 71.5 69.5
> Rods1_L2 52.0 67.5 71.5
>
> The first column are variable names and the 2nd to 4th are the data under
> each variable.
>
> This is just for one patient, so I plan to import from Excel then
> transpose to regular data layout.
> My problem is date and number are mixed, so after tranpose those date
> became something like 1/16/2069. I formatted columns into numeric in Excel
> and the following are my programs:
>
> %MACRO READ(DSN, Vars, Id, DSTrans);
> PROC IMPORT OUT= WORK.&DSN
> DATAFILE= "C:\CBC\TCH\Ellen Elias\SLOS ERG Research.xls"
> DBMS=EXCEL REPLACE;
> RANGE="&DSN.$";
> GETNAMES=NO;
> MIXED=YES;
> SCANTEXT=YES;
> USEDATE=YES;
> SCANTIME=YES;
> RUN;
>
> PROC TRANSPOSE DATA=&DSN OUT=&DSTrans(DROP=_NAME_ _LABEL_);
> ID F1;
> IDLABEL F1;
> VAR &Vars;
> RUN;
>
> RUN;
> %MEND;
> %LET Varlst=F2 F3 F4;
> %READ(Adair, &Varlst, 1, A);
>
> Any suggestions would be greatly appreaciated.
>
> Thanks,
> Sophia
>
From: Suhong Tong on 20 Nov 2009 13:50
Thank you=2C Joe. Your suggestion to use DBDSOPTS leads me searched SAS/Acc=
ess=2C and found a whole bunch of needed information that not documented un=
der PROC IMPORT.

Thank you all for sharing your ideas.
Sophia
Date: Fri=2C 20 Nov 2009 00:51:06 -0600
Subject: Re: Import data from Excel
From: snoopy369(a)gmail.com
To: sophidt(a)hotmail.com
CC: SAS-L(a)listserv.uga.edu

If your data is exactly like that=2C you could do as Arthur says and import=
row 1 in one dataset=2C rows 2+ in another=2C and transpose/merge. =20


You could also transpose it inside of excel (copy=3B paste
special=2C select 'transpose'). Not every solution must be in SAS I suppos=
e :)

If you have dates in other rows=2C and it's too much trouble to import them=
in two separate datasets=2C then try importing everything as character. I=
f you have 9.2 you can use DBDSOPTS to get to DBSASTYPE in PROC IMPORT=2C o=
therwise in 9.1.3 you can use DBSASTYPE directly with libname access=2C but=
I don't think it's available from PROC IMPORT. Force everything to char [=
no idea what happens to the date=2C but it might behave=3B otherwise you ca=
n manually convert it]. If that doesn't work=2C you can import it as numer=
ic [as you have already] and convert the date on your own [subtract=2C hmm=
=2C 20000 or whatever the difference is... it's about 60 years' difference=
=3B I think excel's era is 1899 or thereabouts=2C while SAS's is 1/1/1960].



Finally=2C remember to scream at whomever gave you the data in such a
moronic form. Won't accomplish much=2C but will make you feel better. Unl=
ess it's you=2C in which case rethink your data layouts =3B)

-Joe

On Thu=2C Nov 19=2C 2009 at 5:07 PM=2C Sophia Tong <sophidt(a)hotmail.com> wr=
ote:

Dear listers=2C



I have enjoyed StatTranfer for converting data for many years and now need

to deal with PROC IMPORT again. I have raw data like this:

Date 2/15/2007 4/3/2008 6/18/2009

Rods1_R1 106.7 101.3 64.9

Rods1_L1 125.7 89.4 132.1

Rods1_R2 61.0 71.5 69.5

Rods1_L2 52.0 67.5 71.5



The first column are variable names and the 2nd to 4th are the data under

each variable.



This is just for one patient=2C so I plan to import from Excel then

transpose to regular data layout.

My problem is date and number are mixed=2C so after tranpose those date

became something like 1/16/2069. I formatted columns into numeric in Excel

and the following are my programs:



%MACRO READ(DSN=2C Vars=2C Id=2C DSTrans)=3B

PROC IMPORT OUT=3D WORK.&DSN

DATAFILE=3D "C:\CBC\TCH\Ellen Elias\SLOS ERG Research.xls"

DBMS=3DEXCEL REPLACE=3B

RANGE=3D"&DSN.$"=3B

GETNAMES=3DNO=3B

MIXED=3DYES=3B

SCANTEXT=3DYES=3B

USEDATE=3DYES=3B

SCANTIME=3DYES=3B

RUN=3B



PROC TRANSPOSE DATA=3D&DSN OUT=3D&DSTrans(DROP=3D_NAME_ _LABEL_)=3B

ID F1=3B

IDLABEL F1=3B

VAR &Vars=3B

RUN=3B



RUN=3B

%MEND=3B

%LET Varlst=3DF2 F3 F4=3B

%READ(Adair=2C &Varlst=2C 1=2C A)=3B



Any suggestions would be greatly appreaciated.



Thanks=2C

Sophia


=20
_________________________________________________________________
Bing brings you maps=2C menus=2C and reviews organized in one place.
http://www.bing.com/search?q=3Drestaurants&form=3DMFESRP&publ=3DWLHMTAG&cre=
a=3DTEXT_MFESRP_Local_MapsMenu_Resturants_1x1=
 | 
Pages: 1
Prev: PROC NLP Warning
Next: Multilevel modeling in proc mixed