HELP WITH IMPORTING EXCEL FILE

[Augusto]

Hello everyone

I need a help to create a table from a excel file.
I've attached the excel file that I need to import (called SAS_FILE HAVE 21.10.2015) spreadsheet HAVE and the layout table that I need as the table result Spreadsheed WANT. (i dont need to export, just to import and create a table)

The Tricks are:

NEW COLUMNS NEEEDS TO BE CREATED:

COSIF   = Is the numeric contents from "Destino" column, (must be replicated to all lines that belong to it.)
ASSUNTO = Is the description of the "Destino" column without the numeric contents, (must be replicated to all lines that belong to it.)
VALOR   = Is the sum of the "Debito" e "Credito" columns (sum(debito,credito))
MONTH   = Is the month of the "Date" column with format MMMM
YEAR    = Is the year of the "Date" column with format AAAA
NM_FILE = Is the first word after the first space from the file name

LINES ARE UNNECESSARY TO THE TABLE RESULT
I need to remove from the files these following lines:
Lines with missing values, header, footer.
Lines with values "Saldo do Dia.....................:" "Totais Débito/Crédito..:" "Saldo Anterior.....................:"

Thanks in advance

Augusto Souza

[PaigeMiller]

Have you tried using PROC IMPORT? Does that work for you, or not?

After PROC IMPORT, you can create the additional columns you need in a SAS data step.

[Augusto]

Hi PaigeMiller, thanks for your response.
In fact, i would like a help with this proc import code... if you can write de code, i ll be grateful..

[RW9]

See here, virtually same question (and I note same issue with all caps lock in subject line):

https://communities.sas.com/t5/Base-SAS-Programming/IMPORT-EXCEL-SHEET-WITH-PROPER-INFORMAT/m-p/2308...

[Augusto]

Hi RW9, thanks for participating, I took a look at the post you mentioned, but its not the same issue i have. The issue is not the proc import. The trick is to create the table that is needed with the such informat infile. I need a help to create it.

[Augusto]

TO SUM UP THE ISSUE:

DATA HAVE;

Contrapartida       Destino                   CR             Historico

Conta:                  81703 - DESP         .                  .

12E43                  .                               133              INTEGR

12E43                  .                               133              INTEGR

12E43                  .                               133              INTEGR

Conta:                 817035 - DESP2 . .

12E44                 .                               134               INTEGR

12E44                 .                               134               INTEGR

12E44                 .                               134               INTEGR

...

run;

----- i Need to replicate the value of the Destino column until the next ocurrence

DATA WANT;

Contrapartida    Destino                   CR     Historico

12E43               81703 - DESP        133     INTEGR

12E43               81703 - DESP        133     INTEGR

12E43               81703 - DESP        133     INTEGR

12E44               817035 - DESP2    134     INTEGR

12E44               817035 - DESP2    134     INTEGR

12E44               817035 - DESP2    134     INTEGR

..

run;

[RW9]

Ok, so nothing to do with the Subject then.  It is very simple, look at the "retain" statement in the documentation, or the large amount of examples on the forum, for example, a simple search on "retain" gave this example as number 1 result:

https://communities.sas.com/t5/General-SAS-Programming/retain/m-p/176991/highlight/true#M12396

[Augusto]

The exercise was to import the excel file by proc import and create the columns asked using the best way, but the most important was to replicate the value of the Destino column.

I took a look at the documentation, but sorry, i still not solve the problem.. would you mind coding it?

The Descritpion column has values for the first ocurrence only until the next different ocurrence (like in excel pivot table)

I need to replicate the value of the id (previous) until the next ocurrence.