BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
twisme
Calcite | Level 5

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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   

 

View solution in original post

15 REPLIES 15
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Super User
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
Calcite | Level 5

 

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.

twisme
Calcite | Level 5
exactly
Kurt_Bremser
Super User

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
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5
Thank you! 🙂
twisme
Calcite | Level 5

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 15 replies
  • 1552 views
  • 0 likes
  • 3 in conversation