import excel option

[twisme]

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!

[RW9]

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

[twisme]

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?

[Kurt_Bremser]

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.

[twisme]

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?

[Kurt_Bremser]

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.

[twisme]

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.

[Kurt_Bremser]

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

[twisme]

exactly

[Kurt_Bremser]

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.

[Kurt_Bremser · Accepted solution]

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   

[twisme]

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 😉

[Kurt_Bremser]

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.

[twisme]

Thank you! 🙂

[twisme]

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