How do you count columns in a table using procsql?

I need to test 25 test tables against their 25 production tables to make sure data is the same.  I would like to take a count of the rows and columns.  I have the proc sql in the code area below, and that gives me the count of rows for that table (baby steps).  Does anyone have the code that will count the columns - I do not know how to count those.  Thank you! - Karen
PROC SQL;
	CONNECT TO ODBC (datasrc='xxxxx' uid=xxxxx pwd=xxxxxx);             
	SELECT *
	FROM CONNECTION TO ODBC
	(SELECT count(*)
		FROM PEARL_A.TLTC901_CLH
	);
QUIT;

Open in new window

c9k9hAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bradanelsonCommented:
In the past I have used MACROS give counts on datasets.  Both rows and columns.  I have attached the code below.  Be sure to run the MACRO code first to compile them into your SAS session.  The 2 bottom lines of code will execute the MACRO's.  Be sure to replace [libname].[dataset] with your information.  You can use these MACRO's in many ways, sending the numbers to the log (example below), using the values within desision making MACRO's, or populating other datasets.

Hope this helps.
%MACRO NumberOfVariables(Dataset);
    %LET Dataset_ID=%SYSFUNC(OPEN(&Dataset));
    %IF &Dataset_ID %THEN %DO;
        %SYSFUNC(ATTRN(&Dataset_ID, NVARS))
    %END;
    %ELSE %DO;
        %PUT WARNING: Open for dataset %UPCASE(&Dataset) failed;
        %PUT WARNING: Macro NUMBEROFVARIABLES will return the number of observations as missing.;
        %PUT %SYSFUNC(SYSMSG());
        .
    %END;
    %LET Dataset_ID=%SYSFUNC(CLOSE(&Dataset_ID));
%MEND NumberOfVariables;
 
 
%MACRO NumberOfObservations(Dataset);
    %LET Dataset_ID=%SYSFUNC(OPEN(&Dataset));
    %IF &Dataset_ID %THEN %DO;
        %SYSFUNC(ATTRN(&Dataset_ID, NOBS))
    %END;
    %LET Dataset_ID=%SYSFUNC(CLOSE(&Dataset_ID));
%MEND NumberOfObservations;
 
 
%PUT %NumberOfObservations([libname].[dataset]);
 
%PUT %NumberOfVariables([libname].[dataset]);

Open in new window

0
c9k9hAuthor Commented:
Yes, very helpful - thank you!  Is there a way I can send the output somewhere?  I don't use SAS much - only a beginner here.  
0
autumnwingsCommented:
You can easily send your output to Excel by using ODS. Use the code below to wrap around the output you want to capture.  By changing the extension of the file name you can output different files: .txt, .csv, .html, etc.

Example:

ODS HTML FILE="/FILEPATH/output_file_name.xls" style=minimal " ;
<<insert SAS code>>
ODS HTML CLOSE ;

0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

c9k9hAuthor Commented:
I don't think I'm putting the ODS code you sent in the right place, because as soon as I paste it - the color changes.  Please see below.  This is what I have at this time . . . can you see what is wrong?  Thank you.
PROC SQL;
	CONNECT TO ODBC (datasrc=xxx uid=xxx pwd=xxx);  
	CREATE TABLE test_901 AS
	SELECT *
	FROM CONNECTION TO ODBC
	(SELECT *
		FROM PEARL_A.TLTC901_CLH_PRSST
	);
QUIT;
 
%MACRO NumberOfVariables(Dataset);
    %LET Dataset_ID=%SYSFUNC(OPEN(&Dataset));
    %IF &Dataset_ID %THEN %DO;
        %SYSFUNC(ATTRN(&Dataset_ID, NVARS))
    %END;
    %ELSE %DO;
        %PUT WARNING: Open for dataset %UPCASE(&Dataset) failed;
        %PUT WARNING: Macro NUMBEROFVARIABLES will return the number of observations as missing.;
        %PUT %SYSFUNC(SYSMSG());
        .
    %END;
    %LET Dataset_ID=%SYSFUNC(CLOSE(&Dataset_ID));
%MEND NumberOfVariables;
 
 
%MACRO NumberOfObservations(Dataset);
    %LET Dataset_ID=%SYSFUNC(OPEN(&Dataset));
    %IF &Dataset_ID %THEN %DO;
        %SYSFUNC(ATTRN(&Dataset_ID, NOBS))
    %END;
    %LET Dataset_ID=%SYSFUNC(CLOSE(&Dataset_ID));
%MEND NumberOfObservations;
 
ODS HTML FILE="J:\Projects_FINANCE\LTC_Claims\PEARL\TableCounts.xls" style=minimal " ;
 
  
%PUT %NumberOfObservations(test_901);
 
%PUT %NumberOfVariables(test_901);
 
run;
 
ODS HTML CLOSE ; 

Open in new window

0
autumnwingsCommented:
Yep, you are missing a " around the style option, it should be style="minimal" . Then it should work correctly.
0
c9k9hAuthor Commented:

I'm getting an error.  I put the code below that I'm using.  I hope it's just something simple (again).  Here is the output in the log:

160 PROC SQL;
161 CONNECT TO ODBC (datasrc='tddev' uid=xxxx pwd=XXXXXXX);
162 CREATE TABLE test_901 AS
163 SELECT *
164 FROM CONNECTION TO ODBC
165 (SELECT *
166 FROM PEARL_A.TLTC901_CLH_PRSST
167 );
NOTE: Table WORK.TEST_901 created, with 60862 rows and 19 columns.
168 QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 13.75 seconds
cpu time 1.71 seconds
 
169
170 %MACRO NumberOfVariables(Dataset);
171 %LET Dataset_ID=%SYSFUNC(OPEN(&Dataset));
172 %IF &Dataset_ID %THEN %DO;
173 %SYSFUNC(ATTRN(&Dataset_ID, NVARS))
174 %END;
175 %ELSE %DO;
176 %PUT WARNING: Open for dataset %UPCASE(&Dataset) failed;
177 %PUT WARNING: Macro NUMBEROFVARIABLES will return the number of observations as
177! missing.;
178 %PUT %SYSFUNC(SYSMSG());
179 .
180 %END;
181 %LET Dataset_ID=%SYSFUNC(CLOSE(&Dataset_ID));
182 %MEND NumberOfVariables;
183
184
185 %MACRO NumberOfObservations(Dataset);
186 %LET Dataset_ID=%SYSFUNC(OPEN(&Dataset));
187 %IF &Dataset_ID %THEN %DO;
188 %SYSFUNC(ATTRN(&Dataset_ID, NOBS))
189 %END;
190 %LET Dataset_ID=%SYSFUNC(CLOSE(&Dataset_ID));
191 %MEND NumberOfObservations;
192
193 ODS HTML FILE="J:\Projects_FINANCE\LTC_Claims\PEARL\TableCounts.xls" style="minimal" ;
---------
22
76
ERROR 22-322: Expecting a name.
ERROR 76-322: Syntax error, statement will be ignored.
194
195 %PUT %NumberOfObservations(test_901);
60862
196
197 %PUT %NumberOfVariables(test_901);
19
198
199 run;
200
201 ODS HTML CLOSE ;  

0
c9k9hAuthor Commented:
Whoops!  I forgot to change the variable name in the code you sent.  I'll try that! :)
0
c9k9hAuthor Commented:
It doesn't seem to like the test_901 name I'm giving it.  I tried putting it in quotes, brackets...still getting the error.  Can you see what is wrong?  
 1 PROC SQL;
2 CONNECT TO ODBC (datasrc='tddev' uid=c9k9h pwd=XXXXXXX);
3 CREATE TABLE test_901 AS
4 SELECT *
5 FROM CONNECTION TO ODBC
6 (SELECT *
7 FROM PEARL_A.TLTC901_CLH_PRSST
8 );
NOTE: Table WORK.TEST_901 created, with 60862 rows and 19 columns.
9 QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 13.53 seconds
cpu time 2.82 seconds
 
10
11 %MACRO NumberOfVariables(Dataset);
12 %LET Dataset_ID=%SYSFUNC(OPEN(test_901));
13 %IF &Dataset_ID %THEN %DO;
14 %SYSFUNC(ATTRN(&Dataset_ID, NVARS))
15 %END;
16 %ELSE %DO;
17 %PUT WARNING: Open for dataset %UPCASE(&Dataset) failed;
18 %PUT WARNING: Macro NUMBEROFVARIABLES will return the number of observations as
18 ! missing.;
19 %PUT %SYSFUNC(SYSMSG());
20 .
21 %END;
22 %LET Dataset_ID=%SYSFUNC(CLOSE(&Dataset_ID));
23 %MEND NumberOfVariables;
24
25
26 %MACRO NumberOfObservations(Dataset);
27 %LET Dataset_ID=%SYSFUNC(OPEN(test_901));
28 %IF &Dataset_ID %THEN %DO;
29 %SYSFUNC(ATTRN(&Dataset_ID, NOBS))
30 %END;
31 %LET Dataset_ID=%SYSFUNC(CLOSE(&Dataset_ID));
32 %MEND NumberOfObservations;
33
34 ODS HTML FILE="J:\Projects_FINANCE\LTC_Claims\PEARL\TableCounts.xls" style="minimal" ;
---------
22
76
ERROR 22-322: Expecting a name.
ERROR 76-322: Syntax error, statement will be ignored.
35
36 %PUT %NumberOfObservations(test_901);
60862
37
38 %PUT %NumberOfVariables(test_901);
19
39
40 run;
41
42 ODS HTML CLOSE ;
0
autumnwingsCommented:
Although your test_901 is a temporary dataset and you usually would not need to specify a libname, in this macro it appears to be looking for the libname.

Try using work.test_901 and see if it runs.
0
c9k9hAuthor Commented:
Hmmm...same error:
11 %MACRO NumberOfVariables(Dataset);
12 %LET Dataset_ID=%SYSFUNC(OPEN(work.test_901));
13 %IF &Dataset_ID %THEN %DO;
14 %SYSFUNC(ATTRN(&Dataset_ID, NVARS))
15 %END;
16 %ELSE %DO;
17 %PUT WARNING: Open for dataset %UPCASE(&Dataset) failed;
18 %PUT WARNING: Macro NUMBEROFVARIABLES will return the number of observations as
18 ! missing.;
19 %PUT %SYSFUNC(SYSMSG());
20 .
21 %END;
22 %LET Dataset_ID=%SYSFUNC(CLOSE(&Dataset_ID));
23 %MEND NumberOfVariables;
24
25
26 %MACRO NumberOfObservations(Dataset);
27 %LET Dataset_ID=%SYSFUNC(OPEN(work.test_901));
28 %IF &Dataset_ID %THEN %DO;
29 %SYSFUNC(ATTRN(&Dataset_ID, NOBS))
30 %END;
31 %LET Dataset_ID=%SYSFUNC(CLOSE(&Dataset_ID));
32 %MEND NumberOfObservations;
33
34 ODS HTML FILE="J:\Projects_FINANCE\LTC_Claims\PEARL\TableCounts.xls" style="minimal" ;
---------
22
76
ERROR 22-322: Expecting a name.
ERROR 76-322: Syntax error, statement will be ignored.
35
0
autumnwingsCommented:
Ok, I am going to give you a much simpler macro that will give you the same information.  Try running this code to see if it works.



%macro obsnvars(ds);
 %global dset nvars nobs;
 %let dset=&ds;
 %let dsid = %sysfunc(open(&dset));
 %if &dsid %then
   %do;
      %let nobs =%sysfunc(attrn(&dsid,nobs));
      %let nvars=%sysfunc(attrn(&dsid,nvars));
      %let rc = %sysfunc(close(&dsid));
   %end;
 %else
    %put open for data set &dset failed
         - %sysfunc(sysmsg());
 %mend obsnvars;
 
 %obsnvars(test_901);
ods html file="J:\Projects_FINANCE\LTC_Claims\PEARL\TableCounts.xls" style="minimal" ; 
 %put &dset has &nvars variable(s) and &nobs
      observation(s).;
ods html close ; 

Open in new window

0
c9k9hAuthor Commented:
Same error.  Does it not like the 'temporary' data set?
11 %macro obsnvars(ds);
12 %global dset nvars nobs;
13 %let dset=&ds;
14 %let dsid = %sysfunc(open(&dset));
15 %if &dsid %then
16 %do;
17 %let nobs =%sysfunc(attrn(&dsid,nobs));
18 %let nvars=%sysfunc(attrn(&dsid,nvars));
19 %let rc = %sysfunc(close(&dsid));
20 %end;
21 %else
22 %put open for data set &dset failed
23 - %sysfunc(sysmsg());
24 %mend obsnvars;
25
26 %obsnvars(test_901);
27 ods html file="J:\Projects_FINANCE\LTC_Claims\PEARL\TableCounts.xls" style="minimal" ;
---------
22
76
ERROR 22-322: Expecting a name.
ERROR 76-322: Syntax error, statement will be ignored.
28 %put &dset has &nvars variable(s) and &nobs
29 observation(s).;
test_901 has 19 variable(s) and 60862 observation(s).
30 ods html close ;
0
autumnwingsCommented:
If you run the following do you get an error?



%macro obsnvars(ds);
 %global dset nvars nobs;
 %let dset=&ds;
 %let dsid = %sysfunc(open(&dset));
 %if &dsid %then
   %do;
      %let nobs =%sysfunc(attrn(&dsid,nobs));
      %let nvars=%sysfunc(attrn(&dsid,nvars));
      %let rc = %sysfunc(close(&dsid));
   %end;
 %else
    %put open for data set &dset failed
         - %sysfunc(sysmsg());
 %mend obsnvars;
 %obsnvars(test_901);
 %put &dset has &nvars variable(s) and &nobs
      observation(s).;

Open in new window

0
autumnwingsCommented:
If the above does run correctly, here is the code to get the output into an excel file.


data test_901_count ; ds="&dset" ; nvars=&nvars ; nobs=&nobs ; run ; 
ods html file="J:\Projects_FINANCE\LTC_Claims\PEARL\TableCounts.xls" style="minimal" ; 
proc print data=test_901_count noobs ; run ;
ods html close ;

Open in new window

0
c9k9hAuthor Commented:
I apologize . . . I'm got a bit confused.  Below is the code I just ran.  The error occurs on these two lines:

data test_901_count ; ds="&dset" ; nvars=&nvars ; nobs=&nobs ; run ;
ods html file="J:\Projects_FINANCE\LTC_Claims\PEARL\TableCounts.xls" style="minimal" ;

And this is the error:
194  ods html file="J:\Projects_FINANCE\LTC_Claims\PEARL\TableCounts.xls" style="minimal" ;
                                                                                ---------
                                                                                22
                                                                                76
ERROR 22-322: Expecting a name.

ERROR 76-322: Syntax error, statement will be ignored.

The proc print line runs fine and shows in the output window.


PROC SQL;
	CONNECT TO ODBC (datasrc='tddev' uid=c9k9h pwd=travis3);  
	CREATE TABLE test_901 AS
	SELECT *
	FROM CONNECTION TO ODBC
	(SELECT *
		FROM PEARL_A.TLTC901_CLH_PRSST
	);
QUIT;
 
%macro obsnvars(ds);
 %global dset nvars nobs;
 %let dset=&ds;
 %let dsid = %sysfunc(open(&dset));
 %if &dsid %then
   %do;
      %let nobs =%sysfunc(attrn(&dsid,nobs));
      %let nvars=%sysfunc(attrn(&dsid,nvars));
      %let rc = %sysfunc(close(&dsid));
   %end;
 %else
    %put open for data set &dset failed
         - %sysfunc(sysmsg());
 %mend obsnvars;
 %obsnvars(test_901);
 %put &dset has &nvars variable(s) and &nobs
      observation(s).;
 
data test_901_count ; ds="&dset" ; nvars=&nvars ; nobs=&nobs ; run ; 
ods html file="J:\Projects_FINANCE\LTC_Claims\PEARL\TableCounts.xls" style="minimal" ; 
proc print data=test_901_count noobs ; run ;
ods html close ;

Open in new window

0
c9k9hAuthor Commented:
Whoops!  I'm sorry . . . the error only occurs on this line:
 ods html file="J:\Projects_FINANCE\LTC_Claims\PEARL\TableCounts.xls" style="minimal" ;  
0
bradanelsonCommented:
This may be more than what you need, but I added another MACRO to create a dataset listing all your datasets, rows, and column counts.  To do this, first create a dataset with a list of all your datasets you want counted.  Below is an example:

DATA Datasets;
    LENGTH DatasetName $41.;
    INPUT DatasetName;
    DATALINES;
    SASHELP.Adomsg
    SASHELP.Adsmsg
    SASHELP.Afmsg
    SASHELP.Air
    SASHELP.Asscmgr
    ;
RUN;

Once you replace the 5 datasets I have in the above list, run the attached code.  It will create a 2nd dataset with the info you are looking for.

This is a very clean example and hopefully what you are looking for.  I you want to output the dataset somewhere, I can show you how to impement the ODS stuff you have tried above.

Thanks
%MACRO NumberOfVariables(Dataset);
    %LET Dataset_ID=%SYSFUNC(OPEN(&Dataset));
    %IF &Dataset_ID %THEN %DO;
        %SYSFUNC(ATTRN(&Dataset_ID, NVARS))
    %END;
    %ELSE %DO;
        %PUT WARNING: Open for dataset %UPCASE(&Dataset) failed;
        %PUT WARNING: Macro NUMBEROFVARIABLES will return the number of observations as missing.;
        %PUT %SYSFUNC(SYSMSG());
        .
    %END;
    %LET Dataset_ID=%SYSFUNC(CLOSE(&Dataset_ID));
%MEND NumberOfVariables;
 
%MACRO NumberOfObservations(Dataset);
    %LET Dataset_ID=%SYSFUNC(OPEN(&Dataset));
    %IF &Dataset_ID %THEN %DO;
        %SYSFUNC(ATTRN(&Dataset_ID, NOBS))
    %END;
    %LET Dataset_ID=%SYSFUNC(CLOSE(&Dataset_ID));
%MEND NumberOfObservations;
 
%MACRO DatasetAnalysis(DatasetList,DatasetOut);
    %DO i=1 %TO %NumberOfObservations(&DatasetList);
        DATA _NULL_;
            SET &DatasetList;
            IF _N_=&i;
            CALL SYMPUT('DatasetName',DatasetName);
        RUN;
 
        %LET Rows=%NumberOfObservations(&DatasetName);
        %LET Columns=%NumberOfVariables(&DatasetName);
 
        DATA Temp;
            LENGTH DatasetName $41.;
            DatasetName="&DatasetName";
            Rows=INPUT("&Rows",BEST.);
            Columns=INPUT("&Columns",BEST.);
        RUN;
 
        PROC APPEND BASE=&DatasetOut DATA=Temp; RUN;
    %END;
%MEND;
 
%DatasetAnalysis(Datasets,Datasets2);

Open in new window

0
c9k9hAuthor Commented:
I replaced &Dataset with test_901 in the first two macros.  Is there anything that needs to be changed in the third?  (Thanks for hanging in there with me!)
0
bradanelsonCommented:
Give me a list of datasets you want to find row and column counts for.
0
c9k9hAuthor Commented:
These are the tables I need to test.  I would name the datasets the same.

TLTC901_CLH
TLTC902_ATR
TLTC903_ADL
TLTC904_ICD
TLTC905_FUP
TLTC906_GCV
TLTC908_GIP
TLTC909_GPL
TLTC910_GPN
TLTC911_GPR
TLTC912_GPV
TLTC918_CPL
TLTC919_CIP
TLTC920_CCJ
TLTC921_SMY
TLTC922_CPN
TLTC923_CPR
TLTC924_CPV
TLTC925_RPU
TLTC926_EOB
TLTC927_COR
TLTC928_ROBHIST
TLTC929_AVY_DAW
TLTC930_PYD_DAW
TLTC931_AMT_FAC

Open in new window

0
bradanelsonCommented:
I'm assuming they are all in the WORK libname.  Run the following code without changes.
DATA Datasets;
    LENGTH DatasetName $41.;
    INPUT DatasetName;
    DATALINES;
        TLTC901_CLH
        TLTC902_ATR
        TLTC903_ADL
        TLTC904_ICD
        TLTC905_FUP
        TLTC906_GCV
        TLTC908_GIP
        TLTC909_GPL
        TLTC910_GPN
        TLTC911_GPR
        TLTC912_GPV
        TLTC918_CPL
        TLTC919_CIP
        TLTC920_CCJ
        TLTC921_SMY
        TLTC922_CPN
        TLTC923_CPR
        TLTC924_CPV
        TLTC925_RPU
        TLTC926_EOB
        TLTC927_COR
        TLTC928_ROBHIST
        TLTC929_AVY_DAW
        TLTC930_PYD_DAW
        TLTC931_AMT_FAC
    ;
RUN;
 
 
%MACRO NumberOfVariables(Dataset);
    %LET Dataset_ID=%SYSFUNC(OPEN(&Dataset));
    %IF &Dataset_ID %THEN %DO;
        %SYSFUNC(ATTRN(&Dataset_ID, NVARS))
    %END;
    %ELSE %DO;
        %PUT WARNING: Open for dataset %UPCASE(&Dataset) failed;
        %PUT WARNING: Macro NUMBEROFVARIABLES will return the number of observations as missing.;
        %PUT %SYSFUNC(SYSMSG());
        .
    %END;
    %LET Dataset_ID=%SYSFUNC(CLOSE(&Dataset_ID));
%MEND NumberOfVariables;
 
%MACRO NumberOfObservations(Dataset);
    %LET Dataset_ID=%SYSFUNC(OPEN(&Dataset));
    %IF &Dataset_ID %THEN %DO;
        %SYSFUNC(ATTRN(&Dataset_ID, NOBS))
    %END;
    %LET Dataset_ID=%SYSFUNC(CLOSE(&Dataset_ID));
%MEND NumberOfObservations;
 
%MACRO DatasetAnalysis(DatasetList,DatasetOut);
    %DO i=1 %TO %NumberOfObservations(&DatasetList);
        DATA _NULL_;
            SET &DatasetList;
            IF _N_=&i;
            CALL SYMPUT('DatasetName',DatasetName);
        RUN;
 
        %LET Rows=%NumberOfObservations(&DatasetName);
        %LET Columns=%NumberOfVariables(&DatasetName);
 
        DATA Temp;
            LENGTH DatasetName $41.;
            DatasetName="&DatasetName";
            Rows=INPUT("&Rows",BEST.);
            Columns=INPUT("&Columns",BEST.);
        RUN;
 
        PROC APPEND BASE=&DatasetOut DATA=Temp; RUN;
    %END;
%MEND;
 
%DatasetAnalysis(Datasets,Datasets2);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bradanelsonCommented:
The Datasets2 in the WORK libname will contain the information you want.
0
c9k9hAuthor Commented:
I need to build all the datasets first, correct?  At this point, I only have test_901 built.  So I will do the same for all the others and then test this code.  If I'm on the wrong track - please let me know.  Thank you.
0
c9k9hAuthor Commented:
Wow!  This is a beautiful thing and you are a genius!  Thank you very much for all your help.
0
c9k9hAuthor Commented:
This EE hung in there until a solution was found.  Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.