DATA Step, Macro, Functions and more

import excel option

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

import excel option

Good morning,

I hope somebody could help me.

In a sas code there is a part where an excel file is imported, but I don't understand the logic code very well:

The file imported (called A) has 1 sheet (called B) with 4 colums (col1, col2, col3, col4).

The code that I don't understand is this:

 

%import_to_excel (path = &path_map., file = A , sheet = B, ds_out = B);

data A1 (drop= fmtname2 COLUMN4 default
 					rename=(COLUMN3=label fmtname1=fmtname))
	 A2 (drop= fmtname1 COLUMN3
 					rename=(COLUMN4=label fmtname2=fmtname));

	set B (rename= (COLUMN1=start COLUMN2=end) );	
	retain fmtname1 'formobs' fmtname2 'formrat' type 'n' eexcl 'Y' default 4;

run;
%rcSet(&syserr);

proc format cntlin=A1;
run;
%rcSet(&syserr);
 
 proc format cntlin=A2;
run;
%rcSet(&syserr);

I need that somebody explain to me what appen when file excel is imported and also what kind of trasformation is done with the column 1, 2, 3 e 4? Are all used for a calculation? Or only column 1 and 2 are considered? 

 

Thank you!

 


Accepted Solutions
Solution
‎01-19-2017 08:43 AM
Super User
Posts: 7,863

Re: import excel option

[ Edited ]

So this is what happens when your example code with example data is run:

The code:

data b;
input column1 column2 column3 column4;
cards;
0 0.0003 0.0002 2
0.0003 0.0050 0.0024 5
;
run;

data
  a1 (
    drop=fmtname2 column4 default
    rename=(column3=label fmtname1=fmtname)
  )
  a2 (
    drop=fmtname1 column3
    rename=(column4=label fmtname2=fmtname)
  )
;
set b (rename=(column1=start column2=end));
retain fmtname1 'formobs' fmtname2 'formrat' type 'n' eexcl 'Y' default 4;
run;

proc print data=a1 noobs;
run;
proc print data=a2 noobs;
run;

proc format cntlin=a1;
run;
 
proc format cntlin=a2;
run;

The log:

16         data b;
17         input column1 column2 column3 column4;
18         cards;

NOTE: The data set WORK.B has 2 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      
21         ;

22         run;
23         
24         data
25           a1 (
26             drop=fmtname2 column4 default
27             rename=(column3=label fmtname1=fmtname)
28           )
29           a2 (
30             drop=fmtname1 column3
31             rename=(column4=label fmtname2=fmtname)
32           )
33         ;
34         set b (rename=(column1=start column2=end));
35         retain fmtname1 'formobs' fmtname2 'formrat' type 'n' eexcl 'Y' default 4;
36         run;

NOTE: There were 2 observations read from the data set WORK.B.
NOTE: The data set WORK.A1 has 2 observations and 6 variables.
NOTE: The data set WORK.A2 has 2 observations and 7 variables.
NOTE: DATA statement used (Total process time):
      real time           0.05 seconds
      cpu time            0.02 seconds
      

37         
38         proc print data=a1 noobs;
39         run;

NOTE: There were 2 observations read from the data set WORK.A1.
NOTE: The PROCEDURE PRINT printed page 1.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

40         proc print data=a2 noobs;
41         run;

NOTE: There were 2 observations read from the data set WORK.A2.
NOTE: The PROCEDURE PRINT printed page 2.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

42         
43         proc format cntlin=a1;
NOTE: Format FORMOBS has been output.
44         run;

NOTE: PROCEDURE FORMAT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
NOTE: There were 2 observations read from the data set WORK.A1.

45         
46         proc format cntlin=a2;
NOTE: Format FORMRAT has been output.
47         run;

NOTE: PROCEDURE FORMAT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
NOTE: There were 2 observations read from the data set WORK.A2.

I added two proc print's, so here is the output from them:

      start     end     label    fmtname    type    eexcl

      .0000    .0003    .0002    formobs     n        Y  
      .0003    .0050    .0024    formobs     n        Y  

 start     end     label    fmtname    type    eexcl    default

 .0000    .0003      2      formrat     n        Y         4   
 .0003    .0050      5      formrat     n        Y         4   

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Super User
Posts: 7,997

Re: import excel option

There is nothing in the code you have presented which has nything to do with importing files?  All it does is copy B to A1 and A2 and add some extra variables and then create a format catalog from A1 and A2

Occasional Contributor
Posts: 8

Re: import excel option

Thank you for the answer, but I don't understand:

set B (rename= (COLUMN1=start COLUMN2=end) );

 means that only the column 1 and 2 are used? so if I change the values in column 3 or 4 nothing changes?

Super User
Posts: 7,863

Re: import excel option

[ Edited ]

No. The columns that are used in the rename= dataset option are renamed, all others stay untouched and will "arrive" in the data step as they are.

 

Simply execute the code for testing. As all results are stored in your temporary WORK, no harm can be done.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 8

Re: import excel option

Posted in reply to KurtBremser

Ok,

1) so if I change some values in the excel in the column 3, something changes after the import.

2)  after the code

rename=(COLUMN3=label fmtname1=fmtname))

if now I want to know how and where the column3 is used, what name of it I have to search in the code?

What does the code do there?

Super User
Posts: 7,863

Re: import excel option

data
  A1 (
    drop=fmtname2 COLUMN4 default
    rename=(COLUMN3=label fmtname1=fmtname)
  )
  A2 (
    drop=fmtname1 COLUMN3
    rename=(COLUMN4=label fmtname2=fmtname)
  )
;

In both datasets, label and fmtname will be present.

In dataset A1, they are built from column3 (imported from Excel) and fmtname1 (set in the data step).

In dataset A2, they are built from column4 (imported from Excel) and fmtname2 (set in the data step).

The respective unused variables are dropped from either dataset.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 8

Re: import excel option

Posted in reply to KurtBremser

 

For example:

If excel sheet is composed in this way:

COLUMN1    COLUMN2      COLUMN3      COLUMN4

0                     0,0003              0,0002                2

0,0003            0,0050              0,0024                5 

 

Could you say to me what are the columns and the values in both dataset created?

I'm not a programmer so I need detailed explication.

Super User
Posts: 7,863

Re: import excel option

Run the code and see for yourself. Or do you not have SAS at your disposal?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 8

Re: import excel option

Posted in reply to KurtBremser
exactly
Super User
Posts: 7,863

Re: import excel option

Then I'd strongly recommend you download the SAS University Edition and install it, so you can test your codes and see what happens.

The time when programming was taught with paper and pen is gone for > 40 years now.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎01-19-2017 08:43 AM
Super User
Posts: 7,863

Re: import excel option

[ Edited ]

So this is what happens when your example code with example data is run:

The code:

data b;
input column1 column2 column3 column4;
cards;
0 0.0003 0.0002 2
0.0003 0.0050 0.0024 5
;
run;

data
  a1 (
    drop=fmtname2 column4 default
    rename=(column3=label fmtname1=fmtname)
  )
  a2 (
    drop=fmtname1 column3
    rename=(column4=label fmtname2=fmtname)
  )
;
set b (rename=(column1=start column2=end));
retain fmtname1 'formobs' fmtname2 'formrat' type 'n' eexcl 'Y' default 4;
run;

proc print data=a1 noobs;
run;
proc print data=a2 noobs;
run;

proc format cntlin=a1;
run;
 
proc format cntlin=a2;
run;

The log:

16         data b;
17         input column1 column2 column3 column4;
18         cards;

NOTE: The data set WORK.B has 2 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      
21         ;

22         run;
23         
24         data
25           a1 (
26             drop=fmtname2 column4 default
27             rename=(column3=label fmtname1=fmtname)
28           )
29           a2 (
30             drop=fmtname1 column3
31             rename=(column4=label fmtname2=fmtname)
32           )
33         ;
34         set b (rename=(column1=start column2=end));
35         retain fmtname1 'formobs' fmtname2 'formrat' type 'n' eexcl 'Y' default 4;
36         run;

NOTE: There were 2 observations read from the data set WORK.B.
NOTE: The data set WORK.A1 has 2 observations and 6 variables.
NOTE: The data set WORK.A2 has 2 observations and 7 variables.
NOTE: DATA statement used (Total process time):
      real time           0.05 seconds
      cpu time            0.02 seconds
      

37         
38         proc print data=a1 noobs;
39         run;

NOTE: There were 2 observations read from the data set WORK.A1.
NOTE: The PROCEDURE PRINT printed page 1.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

40         proc print data=a2 noobs;
41         run;

NOTE: There were 2 observations read from the data set WORK.A2.
NOTE: The PROCEDURE PRINT printed page 2.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

42         
43         proc format cntlin=a1;
NOTE: Format FORMOBS has been output.
44         run;

NOTE: PROCEDURE FORMAT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
NOTE: There were 2 observations read from the data set WORK.A1.

45         
46         proc format cntlin=a2;
NOTE: Format FORMRAT has been output.
47         run;

NOTE: PROCEDURE FORMAT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
NOTE: There were 2 observations read from the data set WORK.A2.

I added two proc print's, so here is the output from them:

      start     end     label    fmtname    type    eexcl

      .0000    .0003    .0002    formobs     n        Y  
      .0003    .0050    .0024    formobs     n        Y  

 start     end     label    fmtname    type    eexcl    default

 .0000    .0003      2      formrat     n        Y         4   
 .0003    .0050      5      formrat     n        Y         4   

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 8

Re: import excel option

Posted in reply to KurtBremser

Thank you for your help KurtBremser.

I can't resolve my doubt because I don't have complete vision about that code, but you helped me for what I wrote.

Thank you so much! Have a good day ;-)

Super User
Posts: 7,863

Re: import excel option

Besides installing SAS UE (which might no be trivial, depending on your computer) I can recommend studying the data step principles, the dataset options (mainly drop, keep, rename and where), the retain statement, and proc format.

This should help in understanding what the elements in your code do.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 8

Re: import excel option

Posted in reply to KurtBremser
Thank you! :-)
Occasional Contributor
Posts: 8

Re: import excel option

Posted in reply to KurtBremser

"Simply execute the code for testing".. what is tested?

☑ This topic is solved.

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

Discussion stats
  • 15 replies
  • 234 views
  • 0 likes
  • 3 in conversation