topic Re: HELP WITH IMPORTING EXCEL FILE in SAS Procedures

HELP WITH IMPORTING EXCEL FILE

Augusto — Wed, 21 Oct 2015 13:25:47 GMT

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

Re: HELP WITH IMPORTING EXCEL FILE

PaigeMiller — Wed, 21 Oct 2015 13:43:13 GMT

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.

Re: HELP WITH IMPORTING EXCEL FILE

Augusto — Wed, 21 Oct 2015 13:56:08 GMT

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

Re: HELP WITH IMPORTING EXCEL FILE

RW9 — Wed, 21 Oct 2015 14:18:54 GMT

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/230866

Re: HELP WITH IMPORTING EXCEL FILE

Augusto — Wed, 21 Oct 2015 14:39:02 GMT

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.

Re: HELP WITH IMPORTING EXCEL FILE

Augusto — Wed, 21 Oct 2015 15:03:55 GMT

TO SUM UP THE ISSUE:

DATA HAVE;

Contrapartida Destino CR Historico

Conta: 81703 - DESP . .

12E43 . 133 INTEGR

Conta: 817035 - DESP2 . .

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

12E44 817035 - DESP2 134 INTEGR

run;

Re: HELP WITH IMPORTING EXCEL FILE

RW9 — Wed, 21 Oct 2015 15:22:19 GMT

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

Re: HELP WITH IMPORTING EXCEL FILE

Augusto — Wed, 21 Oct 2015 15:40:05 GMT

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.