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

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@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

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@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
>