BookmarkSubscribeRSS Feed
Augusto
Obsidian | Level 7

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

7 REPLIES 7
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Augusto
Obsidian | Level 7
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
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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.

 

   

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1296 views
  • 0 likes
  • 3 in conversation