BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
BLT2023
Calcite | Level 5
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         %LET CourseRoot = /home/u63571562/BIOS6680_2023;
 70         
 71         OPTIONS DLCREATEDIR;
 72         
 73         LIBNAME Create "&CourseRoot/CDPHE Study";
 NOTE: Libref CREATE was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: /home/u63571562/BIOS6680_2023/CDPHE Study
 74         LIBNAME Create "&CourseRoot/CDPHE Study/Data";
 NOTE: Libref CREATE was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: /home/u63571562/BIOS6680_2023/CDPHE Study/Data
 75         LIBNAME Create "&CourseRoot/CDPHE Study/Data/1_Source";
 NOTE: Libref CREATE was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: /home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source
 76         LIBNAME Create "&CourseRoot/CDPHE Study/Data/2_Import";
 NOTE: Libref CREATE refers to the same physical library as COIMPT.
 NOTE: Libref CREATE was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: /home/u63571562/BIOS6680_2023/CDPHE Study/Data/2_Import
 77         LIBNAME Create "&CourseRoot/CDPHE Study/Data/3_Tabulations";
 NOTE: Libref CREATE was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: /home/u63571562/BIOS6680_2023/CDPHE Study/Data/3_Tabulations
 78         LIBNAME Create "&CourseRoot/CDPHE Study/Data/4_Analysis";
 NOTE: Libref CREATE was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: /home/u63571562/BIOS6680_2023/CDPHE Study/Data/4_Analysis
 79         LIBNAME Create "&CourseRoot/CDPHE Study/Data/5_Results";
 NOTE: Libref CREATE was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: /home/u63571562/BIOS6680_2023/CDPHE Study/Data/5_Results
 80         LIBNAME Create "&CourseRoot/CDPHE Study/Documents";
 NOTE: Libref CREATE was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: /home/u63571562/BIOS6680_2023/CDPHE Study/Documents
 81         LIBNAME Create "&CourseRoot/CDPHE Study/Programs";
 NOTE: Libref CREATE was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: /home/u63571562/BIOS6680_2023/CDPHE Study/Programs
 82         
 83         LIBNAME Create CLEAR;
 NOTE: Libref CREATE has been deassigned.
 84         
 85         OPTIONS NODLCREATEDIR;
 86         
 87         RUN;
 88         
 89         *STEP 2- Create Libraries*;
 90         
 91         LIBNAME CoImpt "&CourseRoot/CDPHE Study/Data/2_Import";
 NOTE: Libref COIMPT was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: /home/u63571562/BIOS6680_2023/CDPHE Study/Data/2_Import
 92         
 93         *STEP 3- Read in Demographic Data*;
 94         
 95         *3.1 - Disabilites*;
 96         DATACoImpt.Disabilities;
 97         INFILE"&CourseRoot/CDPHE Study/Data/1_Source/disabilities.txt" FIRSTOBS = 2 DELIMITER = '&' truncover;
 98         INPUTtract_fips:$10.
 99         pctn_disability:$4.;
 100        RUN;
 
 NOTE: The infile "/home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/disabilities.txt" is:
       Filename=/home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/disabilities.txt,
       Owner Name=u63571562,Group Name=oda,
       Access Permission=-rw-r--r--,
       Last Modified=25Sep2023:12:38:41,
       File Size (bytes)=20327
 
 NOTE: 1242 records were read from the infile "/home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/disabilities.txt".
       The minimum record length was 11.
       The maximum record length was 15.
 NOTE: The data set COIMPT.DISABILITIES has 1242 observations and 2 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.11 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              879.31k
       OS Memory           29284.00k
       Timestamp           09/26/2023 06:35:37 PM
       Step Count                        842  Switch Count  1
       Page Faults                       0
       Page Reclaims                     90
       Page Swaps                        0
       Voluntary Context Switches        41
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 101        
 102        
 102      !  PROC CONTENTS;
 103        
 104        *3.2 - Education*;
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           0.07 seconds
       user cpu time       0.04 seconds
       system cpu time     0.00 seconds
       memory              1833.40k
       OS Memory           29544.00k
       Timestamp           09/26/2023 06:35:37 PM
       Step Count                        843  Switch Count  0
       Page Faults                       0
       Page Reclaims                     137
       Page Swaps                        0
       Voluntary Context Switches        12
       Involuntary Context Switches      3
       Block Input Operations            488
       Block Output Operations           16
       
 
 105        DATACoImpt.Education;
 106        INFILE"&CourseRoot/CDPHE Study/Data/1_Source/Education.txt" FIRSTOBS = 2 DELIMITER = '|';
 107        INPUTpctn1:3.
 108        pctn2:3.
 109        pctn3:3.
 110        pctn4:3.
 111        tract_fips$12.;
 112        RUN;
 
 NOTE: The infile "/home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/Education.txt" is:
       Filename=/home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/Education.txt,
       Owner Name=u63571562,Group Name=oda,
       Access Permission=-rw-r--r--,
       Last Modified=25Sep2023:12:38:41,
       File Size (bytes)=38964
 
 NOTE: 1242 records were read from the infile "/home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/Education.txt".
       The minimum record length was 22.
       The maximum record length was 32.
 NOTE: The data set COIMPT.EDUCATION has 1242 observations and 5 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.05 seconds
       user cpu time       0.01 seconds
       system cpu time     0.01 seconds
       memory              874.34k
       OS Memory           29540.00k
       Timestamp           09/26/2023 06:35:37 PM
       Step Count                        844  Switch Count  1
       Page Faults                       0
       Page Reclaims                     81
       Page Swaps                        0
       Voluntary Context Switches        50
       Involuntary Context Switches      6
       Block Input Operations            0
       Block Output Operations           264
       
 
 113        
 114        
 114      !  PROC CONTENTS;
 115        
 116        *3.3 - Employment*;
 117        LIBNAME Check XLSX "&CourseRoot/CDPHE Study/Data/1_Source/Employment.xlsx";
 NOTE: Libref CHECK was successfully assigned as follows: 
       Engine:        XLSX 
       Physical Name: /home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/Employment.xlsx
 118        LIBNAME Check CLEAR;
 NOTE: Libref CHECK has been deassigned.
 119        
 120        LIBNAME CoImpt XLSX "&CourseRoot/CDPHE Study/Data/1_Source/Employment.xlsx";
 ERROR: Unable to clear or re-assign the library COIMPT because it is still in use.
 ERROR: Error in the LIBNAME statement.
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           0.10 seconds
       user cpu time       0.04 seconds
       system cpu time     0.00 seconds
       memory              965.06k
       OS Memory           29800.00k
       Timestamp           09/26/2023 06:35:37 PM
       Step Count                        845  Switch Count  5
       Page Faults                       0
       Page Reclaims                     95
       Page Swaps                        0
       Voluntary Context Switches        58
       Involuntary Context Switches      4
       Block Input Operations            288
       Block Output Operations           24
       
 
 121        PROC PRINT
 122        DATA = CoImpt.Employment;
 123        LABEL;
 124        RUN;
 
 NOTE: There were 53 observations read from the data set COIMPT.EMPLOYMENT.
 NOTE: PROCEDURE PRINT used (Total process time):
       real time           0.04 seconds
       user cpu time       0.05 seconds
       system cpu time     0.00 seconds
       memory              714.93k
       OS Memory           29540.00k
       Timestamp           09/26/2023 06:35:37 PM
       Step Count                        846  Switch Count  0
       Page Faults                       0
       Page Reclaims                     66
       Page Swaps                        0
       Voluntary Context Switches        8
       Involuntary Context Switches      4
       Block Input Operations            0
       Block Output Operations           16
       
 
 125        
 126        
 126      !  PROC CONTENTS;
 127        
 128        OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 138   

I am a new SAS user working on a class project. I had each piece of this code working, but when I ran it all together to make sure it still worked, I got two errors: 'Unable to clear or re-assign the library COIMPT because it is still in use' and 'Error in the LIBNAME statement.' In results, all of the Proc Contents for Education shows twice, and all of the Proc Contents for Employment show as missing. Despite my efforts, I can't find the mistakes to fix these errors. What are my mistakes, and how can I prevent this from happening again in future steps? Here is the code: 

 

* STEP 1- Create Folders for CDPHE Study*;
 
%LET CourseRoot = /home/u63571562/BIOS6680_2023;
 
OPTIONS DLCREATEDIR;
 
LIBNAME Create "&CourseRoot/CDPHE Study";
LIBNAME Create "&CourseRoot/CDPHE Study/Data";
LIBNAME Create "&CourseRoot/CDPHE Study/Data/1_Source";
LIBNAME Create "&CourseRoot/CDPHE Study/Data/2_Import";
LIBNAME Create "&CourseRoot/CDPHE Study/Data/3_Tabulations";
LIBNAME Create "&CourseRoot/CDPHE Study/Data/4_Analysis";
LIBNAME Create "&CourseRoot/CDPHE Study/Data/5_Results";
LIBNAME Create "&CourseRoot/CDPHE Study/Documents";
LIBNAME Create "&CourseRoot/CDPHE Study/Programs";
 
LIBNAME Create CLEAR;
 
OPTIONS NODLCREATEDIR;
 
RUN;
 
* STEP 2- Create Libraries*;
 
LIBNAME CoImpt "&CourseRoot/CDPHE Study/Data/2_Import";
 
* STEP 3- Read in Demographic Data*;
 
* 3.1 - Disabilites*;
DATA CoImpt.Disabilities;
INFILE "&CourseRoot/CDPHE Study/Data/1_Source/disabilities.txt" FIRSTOBS = 2 DELIMITER = '&' truncover;
INPUT tract_fips :$10.
pctn_disability :$4. ;
RUN;
 
PROC CONTENTS;
 
* 3.2 - Education*;
DATA CoImpt.Education;
INFILE "&CourseRoot/CDPHE Study/Data/1_Source/Education.txt" FIRSTOBS = 2 DELIMITER = '|';
INPUT pctn1 :3.
pctn2 :3.
pctn3 :3.
pctn4 :3.
tract_fips $12. ;
RUN;
 
PROC CONTENTS;
 
* 3.3 - Employment*;
LIBNAME Check XLSX "&CourseRoot/CDPHE Study/Data/1_Source/Employment.xlsx";
LIBNAME Check CLEAR;
 
LIBNAME CoImpt XLSX "&CourseRoot/CDPHE Study/Data/1_Source/Employment.xlsx";
PROC PRINT
DATA = CoImpt.Employment;
LABEL;
RUN;
 
PROC CONTENTS;

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
105        DATA CoImpt.Education;
 106        INFILE"&CourseRoot/CDPHE Study/Data/1_Source/Education.txt" FIRSTOBS = 2 DELIMITER = '|';
 107        INPUTpctn1:3.
 108        pctn2:3.
 109        pctn3:3.
 110        pctn4:3.
 111        tract_fips$12.;
 112        RUN;
 
 NOTE: The infile "/home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/Education.txt" is:
       Filename=/home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/Education.txt,
       Owner Name=u63571562,Group Name=oda,
       Access Permission=-rw-r--r--,
       Last Modified=25Sep2023:12:38:41,
       File Size (bytes)=38964
 
 NOTE: 1242 records were read from the infile "/home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/Education.txt".
       The minimum record length was 22.
       The maximum record length was 32.
 NOTE: The data set COIMPT.EDUCATION has 1242 observations and 5 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.05 seconds
       user cpu time       0.01 seconds
       system cpu time     0.01 seconds
 
 113        
 114        
 114      !  PROC CONTENTS;
 115        
 116        *3.3 - Employment*;
 117        LIBNAME Check XLSX "&CourseRoot/CDPHE Study/Data/1_Source/Employment.xlsx";
 NOTE: Libref CHECK was successfully assigned as follows: 
       Engine:        XLSX 
       Physical Name: /home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/Employment.xlsx
 118        LIBNAME Check CLEAR;
 NOTE: Libref CHECK has been deassigned.
 119        
 120        LIBNAME CoImpt XLSX "&CourseRoot/CDPHE Study/Data/1_Source/Employment.xlsx";
 ERROR: Unable to clear or re-assign the library COIMPT because it is still in use.
 ERROR: Error in the LIBNAME statement.
 

In line 114 of the log, you call PROC CONTENTS, but you don't end it with a run; statement. So when it comes to the LIBNAME, data set COIMPT.EDUCATION is still in use by PROC CONTENTS; and so you cannot re-assign the libname while it is in use. You can fix this with

 

proc contents;
run;

 

Using the run; statement also fixes the PROC CONTENTS for Employment. The PROC CONTENTS doesn't execute until it comes to a run; but the program ends before that.

 

Using a run; to end all PROCs (except for SQL, GLM, REG and a few others) is a very good habit to get into.

 

Additional good things to get into the habit of doing: do not keep re-using the same libname to refer to multiple different data sources.

--
Paige Miller

View solution in original post

8 REPLIES 8
PaigeMiller
Diamond | Level 26

When you have errors in your code, show us the ENTIRE log.

 

Please copy the entire log as text and then click on the </> icon and paste the log into the window that appears.

PaigeMiller_0-1663012019648.png

--
Paige Miller
PaigeMiller
Diamond | Level 26
105        DATA CoImpt.Education;
 106        INFILE"&CourseRoot/CDPHE Study/Data/1_Source/Education.txt" FIRSTOBS = 2 DELIMITER = '|';
 107        INPUTpctn1:3.
 108        pctn2:3.
 109        pctn3:3.
 110        pctn4:3.
 111        tract_fips$12.;
 112        RUN;
 
 NOTE: The infile "/home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/Education.txt" is:
       Filename=/home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/Education.txt,
       Owner Name=u63571562,Group Name=oda,
       Access Permission=-rw-r--r--,
       Last Modified=25Sep2023:12:38:41,
       File Size (bytes)=38964
 
 NOTE: 1242 records were read from the infile "/home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/Education.txt".
       The minimum record length was 22.
       The maximum record length was 32.
 NOTE: The data set COIMPT.EDUCATION has 1242 observations and 5 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.05 seconds
       user cpu time       0.01 seconds
       system cpu time     0.01 seconds
 
 113        
 114        
 114      !  PROC CONTENTS;
 115        
 116        *3.3 - Employment*;
 117        LIBNAME Check XLSX "&CourseRoot/CDPHE Study/Data/1_Source/Employment.xlsx";
 NOTE: Libref CHECK was successfully assigned as follows: 
       Engine:        XLSX 
       Physical Name: /home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/Employment.xlsx
 118        LIBNAME Check CLEAR;
 NOTE: Libref CHECK has been deassigned.
 119        
 120        LIBNAME CoImpt XLSX "&CourseRoot/CDPHE Study/Data/1_Source/Employment.xlsx";
 ERROR: Unable to clear or re-assign the library COIMPT because it is still in use.
 ERROR: Error in the LIBNAME statement.
 

In line 114 of the log, you call PROC CONTENTS, but you don't end it with a run; statement. So when it comes to the LIBNAME, data set COIMPT.EDUCATION is still in use by PROC CONTENTS; and so you cannot re-assign the libname while it is in use. You can fix this with

 

proc contents;
run;

 

Using the run; statement also fixes the PROC CONTENTS for Employment. The PROC CONTENTS doesn't execute until it comes to a run; but the program ends before that.

 

Using a run; to end all PROCs (except for SQL, GLM, REG and a few others) is a very good habit to get into.

 

Additional good things to get into the habit of doing: do not keep re-using the same libname to refer to multiple different data sources.

--
Paige Miller
BLT2023
Calcite | Level 5

Thank you! This removed the errors from the code, and I see what I did wrong and how I can improve.

However, I am graded on the proc contents, and this did not fix what appears in results. When I run just the piece of code for 'employment':

LIBNAME Check XLSX "&CourseRoot/CDPHE Study/Data/1_Source/Employment.xlsx";
LIBNAME Check CLEAR;

LIBNAME CoImpt XLSX "&CourseRoot/CDPHE Study/Data/1_Source/Employment.xlsx";
PROC PRINT
		DATA = CoImpt.Employment;
		LABEL;
	RUN;

	PROC CONTENTS;
	RUN;

It gives me the proc contents for 'education,' and with no observations (see photo). What is the reason for this? Why would I not be seeing the two numeric values that are in the employment data? Thank you for your feedback. 

Tom
Super User Tom
Super User

Your code does not make much sense at all.

You define a libref and then clear it.

LIBNAME Check XLSX "&CourseRoot/CDPHE Study/Data/1_Source/Employment.xlsx";
LIBNAME Check CLEAR;

Since SAS does not know if the meant to use CHECK to create and XLSX file or read from an existing XLSX file the first libname will work whether or not the file specified exists or not.

That might have a side effect of creating an empty XLSX file when the named file does not exist.

You then have another libref pointing at the exact same file.

LIBNAME CoImpt XLSX "&CourseRoot/CDPHE Study/Data/1_Source/Employment.xlsx";

Then a PROC PRINT step with an invalid statement in the middle of it (and a lot of random spaces).

PROC PRINT
		DATA = CoImpt.Employment;
		LABEL;
	RUN;

Perhaps you meant to include the LABEL option on the PROC PRINT statement?

You can save yourself a lot of headaches if you get in the habit of placing the semicolon that ends a multiple line statement on a new line by itself where it is more obvious when scanning the code.

PROC PRINT
  DATA = CoImpt.Employment
  LABEL
;
RUN;

Then you run a PROC CONTENTS but you don't tell PROC CONTENTS what dataset to run on. So it will run on the last dataset you created, but we cannot tell what dataset that would be since none of the code you posted created any datasets.  Perhaps you meant:

proc contents data=CoImpt.Employment;
run;

 

PaigeMiller
Diamond | Level 26

@BLT2023 wrote:

Thank you! This removed the errors from the code, and I see what I did wrong and how I can improve.

However, I am graded on the proc contents, and this did not fix what appears in results. When I run just the piece of code for 'employment':

LIBNAME Check XLSX "&CourseRoot/CDPHE Study/Data/1_Source/Employment.xlsx";
LIBNAME Check CLEAR;

LIBNAME CoImpt XLSX "&CourseRoot/CDPHE Study/Data/1_Source/Employment.xlsx";
PROC PRINT
		DATA = CoImpt.Employment;
		LABEL;
	RUN;

	PROC CONTENTS;
	RUN;

It gives me the proc contents for 'education,' and with no observations (see photo). What is the reason for this? Why would I not be seeing the two numeric values that are in the employment data? Thank you for your feedback. 


When you don't specify a data set for PROC CONTENTS, it (and all SAS PROCs) work on the most recently created data set. A LIBNAME does not create a data set. You really ought to use for PROC CONTENTS and specify the data set name.

 

proc contents data=CoImpt.Employment;
run;

This is another habit that you should get into and do without fail.

--
Paige Miller
BLT2023
Calcite | Level 5

This makes sense, and works in my code. However, it replaces the number of observations in the results with "." How do I account for this? Thank you for answering my questions!

Tom
Super User Tom
Super User

PROC CONTENTS does not know the number of observations in foreign databases.  It can only do that for actual SAS datasets. Since your code is using the foreign database engine of XLSX to point to a sheet in an Excel file then PROC CONTENTS will not display the number of observations.

 

If you want the number of observations you will have to either count them yourself (like select count(*) from ..... in PROC SQL code). 

 

Or copy the sheet to an actual SAS dataset, perhaps in the WORK library, and run PROC CONTENTS on that actual SAS dataset.

BLT2023
Calcite | Level 5

Got it, now I understand. Thank you so much for the clarification.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 1867 views
  • 0 likes
  • 3 in conversation