Format Transfer

Accepted Solution Solved
Reply
Contributor ZZB
Contributor
Posts: 34
Accepted Solution

Format Transfer

[ Edited ]

The following is the format in my excel. How to transfer to SAS format?

 

Excel:

Date          ID1    ID2   ID3  ID4 ...........

2000/01    Ret    Ret  .......

2000/02    Ret    Ret .....

2000/03    Ret    Ret  ......

........

........

 

SAS:

Date         Ret 

2000/01   ID1Ret

2000/02   ID1Ret

2000/03   ID1Ret

.....

2000/01   ID2Ret

2000/02   ID2Ret

2000/03   ID2Ret

.....

2000/01   ID3Ret

2000/02   ID3Ret

2000/03   ID3Ret

.....

 


Accepted Solutions
Solution
‎05-04-2016 05:09 PM
Respected Advisor
Posts: 4,606

Re: Format Transfer

Your main problem is when importing your data file.  I suggest saving your file as a TAB delimited text file (Brazil.txt) and processing it this way:

 

PROC IMPORT OUT= WORK.brazil 
    DATAFILE= "&sasforum.\Datasets\Brazil.txt" 
    DBMS=TAB REPLACE;
GETNAMES=YES;
DATAROW=2; 
GUESSINGROWS=999; 
RUN;

proc transpose data=brazil out=temp;
by date;
var _character_;
run;

data want;
set temp;
value = input(COL1, ?? percent10.);
drop COL1;
rename _name_= Ret;
run;
PG

View solution in original post


All Replies
Respected Advisor
Posts: 4,606

Re: Format Transfer

Build on:

 

proc transpose data=myExcelData out=mySASdata;

by date;

var id: ;

run;

 

proc sort ...

 

PG
Contributor ZZB
Contributor
Posts: 34

Re: Format Transfer

[ Edited ]

Only one ID exists in the new dataset if using your code.

 

I attach my Excel.

 

 

Grand Advisor
Posts: 17,338

Re: Format Transfer

Show your code and log

Contributor ZZB
Contributor
Posts: 34

Re: Format Transfer

After I import my excel into SAS.

 

proc transpose data=excel out=sas;

by date;

var id: ;

run;

 

 

Solution
‎05-04-2016 05:09 PM
Respected Advisor
Posts: 4,606

Re: Format Transfer

Your main problem is when importing your data file.  I suggest saving your file as a TAB delimited text file (Brazil.txt) and processing it this way:

 

PROC IMPORT OUT= WORK.brazil 
    DATAFILE= "&sasforum.\Datasets\Brazil.txt" 
    DBMS=TAB REPLACE;
GETNAMES=YES;
DATAROW=2; 
GUESSINGROWS=999; 
RUN;

proc transpose data=brazil out=temp;
by date;
var _character_;
run;

data want;
set temp;
value = input(COL1, ?? percent10.);
drop COL1;
rename _name_= Ret;
run;
PG
Grand Advisor
Posts: 10,210

Re: Format Transfer

For future reference, it may be helpful to know that "Format" has a fairly specific meaning in SAS related to display of individual values. This topic might be better referenced as file or dataset Layout or structure.

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 492 views
  • 1 like
  • 4 in conversation