topic Re: import excel option in SAS Programming

import excel option

twisme — Wed, 18 Jan 2017 14:16:59 GMT

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!

Re: import excel option

RW9 — Wed, 18 Jan 2017 14:22:49 GMT

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

Re: import excel option

Kurt_Bremser — Wed, 18 Jan 2017 14:27:58 GMT

No calculations are done. The contents of sheet B are used as definition files for two numeric formats (formobs. and formrat.).

The dataset B (imported from Excel, as I guess) has two format definitions side-by-side. The data step splits that vertically into two datasets, one from the columns 1&3, the other from columns 2&4. Both datasets contain the same columns due to the respective drop= and rename= dataset options.

These datasets now have a structure that allows their use as input files for proc format.

Re: import excel option

twisme — Wed, 18 Jan 2017 14:39:54 GMT

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?

Re: import excel option

Kurt_Bremser — Wed, 18 Jan 2017 14:43:36 GMT

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.

Re: import excel option

twisme — Wed, 18 Jan 2017 14:49:33 GMT

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?

Re: import excel option

Kurt_Bremser — Wed, 18 Jan 2017 14:57:43 GMT

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.

Re: import excel option

twisme — Wed, 18 Jan 2017 15:16:04 GMT

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.

Re: import excel option

twisme — Wed, 18 Jan 2017 17:35:56 GMT

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

Re: import excel option

Kurt_Bremser — Wed, 18 Jan 2017 19:25:27 GMT

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

Re: import excel option

twisme — Thu, 19 Jan 2017 10:38:10 GMT

exactly

Re: import excel option

Kurt_Bremser — Thu, 19 Jan 2017 11:40:20 GMT

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.

Re: import excel option

Kurt_Bremser — Thu, 19 Jan 2017 11:59:16 GMT

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

Re: import excel option

twisme — Thu, 19 Jan 2017 13:43:01 GMT

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 😉

Re: import excel option

Kurt_Bremser — Thu, 19 Jan 2017 15:12:24 GMT

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.

Re: import excel option

twisme — Thu, 19 Jan 2017 16:47:19 GMT

Thank you! 🙂