f



how to output number of missing values of a text variable on a table?

hello

proc means or proc tabulate give quickly number of missing value for
continuous variables on a specific table.



I would like to do the same with a text variable. i would like to
export on a table the number of missing value for my variable.

0
10/21/2008 7:21:54 PM
comp.soft-sys.sas 142828 articles. 3 followers. Post Follow

2 Replies
406 Views

Similar Articles

[PageSpeed] 40

How about something like this:

data missing ;
 input a 1 b$ 3 c 5 ;
 cards ;
1 a	1
2 . 2
3 a .
.. a 4
5 a 5
6 . .
.. a 7
8 a .
9 . 9
;

proc sql ;
	select case
		when type='num'  then 'sum(' || name || '= . ) as '  || name
		when type='char' then 'sum(' || name || "= '' ) as " || name
	  end
		into :syntax separated by ", "
	from dictionary.columns
	where Libname = "WORK" AND MemName = "MISSING" ;
quit ;

proc sql ;
	select &Syntax
	from Missing ;
quit ;
0
karovaldas (130)
10/21/2008 10:15:25 PM
On Oct 21, 8:21=A0pm, sofiane <mesbah.sofi...@gmail.com> wrote:
> hello
>
> proc means or proc tabulate give quickly number of missing value for
> continuous variables on a specific table.
>
> I would like to do the same with a text variable. i would like to
> export on a table the number of missing value for my variable.

Proc freq nlevels will allow you to summarise your missing/non-missing
values. You can specify a subset of character variables if you are
only interested in them.
HTH

data have;
   input A B$8-10;
   cards;
1      X
2
       Y
4      F
;run;

ods select nlevels;
ods output nlevels=3Dnlevels;
proc freq data=3Dhave(keep=3D_character_) levels;
run;
ods output close;
0
dorjetarap (477)
10/21/2008 10:23:30 PM
Reply:

Similar Artilces:

Re: how to output number of missing values of a text variable on
hi ... how about proc freq ... data test ; input a 1 b : $3. c 5 name : $5; datalines; 1 a 1 mike 2 . 2 sara 3 a . . .. a 4 barack 5 a 5 armondo 6 . . . .. a 7 . 8 a . . 9 . 9 . ; run; proc format; value $miss ' ' = 'MISSING' other='NOT MISSING'; run; proc freq data=test; table _character_ / missing; format _character_ $miss.; run; to "export on a table" (maybe in Excel) use ODS ... ods html file='z:\missing.html'; proc freq data=test; table _character_ / missing; format _character_ $miss.; run; ods html close; -- Mike Zdeb U@Albany School of Public Health One University Place Rensselaer, New York 12144-3456 P/518-402-6479 F/630-604-1475 > hello > > proc means or proc tabulate give quickly number of missing value for > continuous variables on a specific table. > > > > I would like to do the same with a text variable. i would like to > export on a table the number of missing value for my variable. > > ...

Re: how to output number of missing values of a text variable on #3
One more think I do not know about SQL. There are so many. On 10/22/08, Haris <Karovaldas@gmail.com> wrote: > Good one, Data _Null_, NMISS function seems to work equally well for > NUM and CHAR variables and eliminates the need for SUM! Runs faster > too! > > proc sql; > select catx(' ','nmiss(',name,') as', name) > into :syntax separated by ", " > from dictionary.columns > where Libname = "WORK" AND MemName = "MISSING" ; > > %put NOTE: GENERATED SYNTAX: %bquote(&syntax); > > title 'Missing' ; > select &Syntax > from Missing; > quit; > > On Oct 21, 5:30 pm, iebup...@GMAIL.COM ("./ ADD NAME=Data _null_,") > wrote: > > Using the MISSING function this can be simplified somewhat. > > > > proc sql; > > select catx(' ','sum(missing(',name,')) as', name) > > into :syntax separated by ", " > > from dictionary.columns > > where Libname = "WORK" AND MemName = "MISSING" ; > > %put NOTE: GENERATED SYNTAX: %bquote(&syntax); > > select &Syntax > > from Missing; > > quit; > > > > On 10/21/08, Haris <Karoval...@gmail.com> wrote: > > > > > How about something like this: > > > > > data missing ; > > > input a 1 b...

Re: how to output number of missing values of a text variable on #2
Using the MISSING function this can be simplified somewhat. proc sql; select catx(' ','sum(missing(',name,')) as', name) into :syntax separated by ", " from dictionary.columns where Libname = "WORK" AND MemName = "MISSING" ; %put NOTE: GENERATED SYNTAX: %bquote(&syntax); select &Syntax from Missing; quit; On 10/21/08, Haris <Karovaldas@gmail.com> wrote: > How about something like this: > > data missing ; > input a 1 b$ 3 c 5 ; > cards ; > 1 a 1 > 2 . 2 > 3 a . > . a 4 > 5 a 5 > 6 . . > . a 7 > 8 a . > 9 . 9 > ; > > proc sql ; > select case > when type='num' then 'sum(' || name || '= . ) as ' || name > when type='char' then 'sum(' || name || "= '' ) as " || name > end > into :syntax separated by ", " > from dictionary.columns > where Libname = "WORK" AND MemName = "MISSING" ; > quit ; > > proc sql ; > select &Syntax > from Missing ; > quit ; > Good one, Data _Null_, NMISS function seems to work equally well for NUM and CHAR variables and eliminates the need for SUM! Runs faster too! proc sql; select catx(' ','nmiss(',name,') as', name) into :syntax separated by ...

Re: how to output number of missing values of a text variable on #4
On Wed, 22 Oct 2008 07:44:17 -0700, Haris <Karovaldas@GMAIL.COM> wrote: >Good one, Data _Null_, NMISS function seems to work equally well for >NUM and CHAR variables Not just NMISS. Also N, MAX, and MIN. But that's only in PROC SQL, and only when the functions are applied vertically (that is, with a single argument to be evaluated for each row in the source or each row in the GROUP BY segment). When these functions are used outside PROC SQL, or inside PROC SQL with two or more arguments, they accept only numeric arguments. >and eliminates the need for SUM! Runs faster too! > >proc sql; > select catx(' ','nmiss(',name,') as', name) > into :syntax separated by ", " > from dictionary.columns > where Libname = "WORK" AND MemName = "MISSING" ; > > %put NOTE: GENERATED SYNTAX: %bquote(&syntax); > > title 'Missing' ; > select &Syntax > from Missing; >quit; > >On Oct 21, 5:30 pm, iebup...@GMAIL.COM ("./ ADD NAME=Data _null_,") >wrote: >> Using the MISSING function this can be simplified somewhat. >> >> proc sql; >> select catx(' ','sum(missing(',name,')) as', name) >> into :syntax separated by ", " >> from dictionary.columns >> where Libname = "WORK" AND MemName = "MISSING" ; >> %put NOTE: GE...

Somewhat OT: SAS output in LaTeX
Hi all - I managed to get my SAS freq tables into LaTeX, thanks to the ods tagsets.latex2 info in the SAS-L archives (and copying the appropriate proc template from the SAS site). So I have very nice looking tables now. Ideally what we want is to take those numbers and put them into text, in a repeatable format. (So having written the code for SAS and LaTeX, we could give this all back to the generating department to run every few months, and we don't need to do it ourselves). i.e. I have a frequency table with 100 men, 60 women, 40 didn't answer. ...

Re: How to output number of missing character variables to a file #2
You should consider using the ODS table OneWayFreqs. It contains all the one way tables for each variable listed in the TABLES statement or with no TABLES statement all variables in the input data. You don't need to combine all those individual tables, etc. etc. better faster easier. I think maybe. On 2/3/10, Jim Groeneveld <jim.1stat@yahoo.com> wrote: > Hi Lance, > > In the summer of 2004 I already was preparing a macro (MULTFREQ) that would > present the frequencies of many variables in one table. (Because of always > more utgent work that macro hasn't been f...

Re: Making a table of % of missing values for several variables #3
One proc tabulate can get what you need, if you don't mind some extra info: data have; input snp1 $ snp2 $ snp3 $; cards; a . g a . h .. a . .. a l b . . .. b . .. . k c . . c . a .. b b ; run; proc tabulate noseps missing; class snp1-snp3; table snp1-snp3, pctn='%'; run; ------------------------------------- | | % | |----------------------+------------| |snp1 | | | | 50.00| |a | 20.00| |b | 10.00| |c | 20.00| |snp2 ...

Re: How to output number of missing character variables to a file #5
Something like this may work: proc sql noprint; select 'sum('!!trim(name)!!' is null)/count(*) as '!!trim(name)!! ' format=percent10.2' into :vars separated by ',' from dictionary.columns where memname='XXXX' and libname='YYYY' and name like 'SNP%' and type='char'; create table test as select &vars from YYYY.XXXX; proc transpose name=variable out=pctmiss(rename=(col1=pctmiss));run; Regards, Søren On Tue, 2 Feb 2010 21:08:46 -0800, Lance Smith <medicaltrial@GMAIL.COM> wrote: >Hi > >...

Re: Making a table of % of missing values for several variables #2
Lance, Yet another array way: Sample Data: %let N = 2010; data have; array s[*] $1 SNP1 - SNP50; do id = 1 to &N; do i = 1 to 50; if ranuni(123) > .3 then s[i] = '1'; else s[i] = ' '; end; output; end; drop i; run; Array solution: data need; array k[50] _temporary_ ; do until(eof); set have end = eof; array s[*] $1 SNP1 - SNP50; do _n_ = 1 to dim(k); if s[_n_] ne ' ' then k[_n_] ++ 1; end; end; do i = 1 to dim(k); k[i] = (&N - k[i]) / &N * 100; Name = vname(s[i]); Missing = k[i]; output; end; keep Na...

how to incorporate the value of one variable into a sas variable
Hello, I have data as follows id prod_code bal 1 boat 100 1 car 200 2 he 40 3 car 100 Basically this is bal per customer by account type. What I need is one record per id, as id bal_boat bal_car bal_he 1 100 200 . 2 . . 40 3 . 100 . Since I have alot of product codes, I would like a macro approach that will automate and customize the naming of the variables in output data set. I read with interest the transpose solution give in the recent thread "splitting an recombining a dataset" and I had intended to go with that approach, using proc expand to deal with probable non-existant account types at the id level (eg not all ids will have all prod codes). However, my collegue senses this would be, at the least, not an elegant approach. Many thanks in advance for you time! T.J. Elle On Jul 24, 4:06=EF=BF=BDpm, tj.el...@SUNTRUST.COM ("T.J. Eller") wrote: > Hello, > > I have data as follows > > id prod_code bal > 1 =EF=BF=BDboat =EF=BF=BD =EF=BF=BD =EF=BF=BD =EF=BF=BD100 > 1 =EF=BF=BDcar =EF=BF=BD =EF=BF=BD =EF=BF=BD200 > 2 =EF=BF=BDhe =EF=BF=BD =EF=BF=BD =EF=BF=BD =EF=BF=BD 40 > 3 =EF=BF=BD car =EF=BF=BD =EF=BF=BD =EF=BF=BD 100 > > Basically this is bal per customer by account type. > > What I need is one record per id, as > > id bal_boat bal_car bal_he > 1 =EF=BF=BD 100 =EF=BF=BD =EF=BF=BD =EF=BF=BD200 =EF=BF=BD =EF=BF=BD =EF= =BF=B...

Extract numbers from table containing text and numbers
Hello, I am trying to extract 2 columns from a file with 425 header lines and then the following: Q (upper-edge --0.1250E-03 AmP )= 0.98880E-02+- 0.28300E-03 units/alpha/round Q (upper-edge --0.2500E-03 AmP )= 0.19648E-01+- 0.39412E-03 units/alpha/round Q (upper-edge --0.3750E-03 AmP )= 0.18384E-01+- 0.38318E-03 units/alpha/round Q (upper-edge --0.5000E-03 AmP )= 0.17536E-01+- 0.37040E-03 units/alpha/round Q (upper-edge --0.6250E-03 AmP )= 0.16064E-01+- 0.36000E-03 units/alpha/round Q (upper-edge --0.7500E-03 AmP )= 0.15888E-01+- 0.35954E-03 units/alpha/round Here is my attempt, using textscan, and the output I am getting: >> clear >> clc >> fid1=fopen('rqm7yxk1.out','rt'); >> fmt1=[' %s' '%s' '%s%s%f' '%s' '%s' ' %f%s%s' ' %f' '%s']; >> set_m=textscan(fid1, fmt1,'delimiter',' ','headerlines',425,'CollectOutput',true); %skip 425 header lines and then read in the data >> fclose(fid1); >> set_m set_m = Columns 1 through 4 {1x4 cell} [0x1 double] {0x1 cell} [0x1 double] Columns 5 through 7 {0x2 cell} [0x1 double] {0x1 cell} >> set_m{1} ans = '' 'E' '(upper-edge' '--0.1250E-03' Question: I need the 3 columns that contain the numbers. Only the first 4 colu...

Drop variables variables with 100% missing values
Hi All, I have a dataset with many variables with 100% missing values. I want to drop all the varibales in my SAS dataset with 100% missing values. I want to automate this process as i am dealing with 300+ variables. I am currently using following code but it takes a lot of time. Is there any other easy way top do it? %macro misscheck(dsn=); proc contents data=&dsn noprint out=contents; run; proc sql noprint; select trim(name) into:VLIST separated by ',' from contents; quit; proc sql noprint; select count(name) into:cnt from contents; quit; %do i=1 %TO &cnt; %let var...

Using the value of a SAS variable to select another variable
Hello, I was wondering if it is possible in SAS to use the value of one variable in order to pick a value from a different variable in a DATA step. For example, if I have 4 fields, V1, P1, P2, P3, I would want to have the code do this:: If V1=1, then pick the value from P1 if V1=2, then pick the value from P2 if V1=3, then pick the value from P3 The solution is obvious if there are only 3 fields, and their name is fixed, but in my case both the number of fields to pick for (P1-P3 in my example), can vary, and the name of those fields can vary too. I could run a macro to rename all the fiel...

Re: Drop variables variables with 100% missing values
I like PROC FREQ, plus a well chosen value labeling FORMAT, for this. It is easy to code, but if your data is huge it may not be the best choice. data one; input x1 x2 x3 x4:$1. x5 c1 $ c2 $; cards; . 3 5 . . a a .d 2 5 . . a b . 3 4 . .c a c . 5 . . . a d . 1 6 . . a . . 1 5 . . a d . 3 5 . . a e ;;;; run; proc print; run; proc format; value allmiss .-.z=. other=1; value $allmiss ' '=' ' other='1'; run; ods listing close; proc freq; tables _all_; format _character_ $allmiss. _numeric_ allmiss.; ods output OneWayFreqs=OneWayFreqs(keep=table...

Re: Using the value of a SAS variable to select another variable
> From: myself > Subject: Re: Using the value of a SAS variable to select > another variable > > Hello again, > > I am now running into the 'reverse' problem, namely:, keeping > with my previous example, with the fields V1, P1, P2 and P3, > I need my code to do this, depending on the value in the > field V1 > If V1=1 then assign new values (which I calculate) > to the variables P2 and P3, > If V1=2 then assign new value to > P3 and so on. ah, this does not read as systematic > Again, I have more than just 3 fields P's I have 38 >...

Re: Drop variables variables with 100% missing values #4
I'm sure that there are much better examples in the list, but an idea for nums. If you need it also for chars, you might not add them but use !! instead. To let it not get too long, you could compress the parts. It is also not necessary to get it too long, because if there is something other than " " in the first byte, it is not all blank: data test; set sashelp.class; x=.; y=.; if sex="F" then weight=.; run; data _null_; set test end=eof; array t(*) _numeric_; length list $500; do i=1 to dim(t); t(i)+t(i); end; if eof then do; do i=1 to di...

Re: Drop variables variables with 100% missing values #2
If all of your variables are numeric, the following code will work and is relatively simple. Data nomiss; input a b c; char = 'abc'; cards; 1 2 . 2 3 . 4 . . run; Data nomiss; set nomiss; card+1;*create a unique identifier; run; Proc Transpose out = nomiss ( where = (col1 ne .)); by card; run; Proc Transpose out = nomiss (drop = card _name_ ) ; by card; run; proc print; run; Nat Wooding Environmental Specialist III Dominion, Environmental Biology 4111 Castlewood Rd Richmond, VA 23234 Phone:804-271-5313, Fax: 804-271-2977 Kunal Kelkar ...

Re: Drop variables variables with 100% missing values #3
You might want to try the function nmiss() with proc sql or the nmiss statistics with proc mean. They are much simpler and may be faster. Hope this helps. LL On Jan 29, 2008 6:25 AM, Kunal Kelkar <mailtokunalkelkar@gmail.com> wrote: > Hi All, > > I have a dataset with many variables with 100% missing values. I want to > drop all the varibales in my SAS dataset with 100% missing values. I want to > automate this process as i am dealing with 300+ variables. > > I am currently using following code but it takes a lot of time. Is there any > other easy way top d...

Re: Drop variables variables with 100% missing values #6
On Tue, 29 Jan 2008 16:55:45 +0530, Kunal Kelkar <mailtokunalkelkar@GMAIL.COM> wrote: >Hi All, > > I have a dataset with many variables with 100% missing values. I want to >drop all the varibales in my SAS dataset with 100% missing values. I want to >automate this process as i am dealing with 300+ variables. > >I am currently using following code but it takes a lot of time. Is there any >other easy way top do it? > >%macro misscheck(dsn=); > >proc contents data=&dsn noprint out=contents; >run; > >proc sql noprint; >select trim(name)...

missing table numbers
I use LaTeX on Solaris. For years now I've had an annoying problem of missing table numbers. For example, I have a label defined as "\label{tbl:units}", and I wrote "In this paper, the default units will be as shown in Table \ref{tbl:units} unless otherwise specified." In the output, the table number does not appear in the sentence. I must resort to typing it in -- and remembering to change it if I insert a table before it. This happens for every table I refer to using \ref. I had been using an old version of LaTeX from 1999. My sysadmin is very busy, and I'm the only person around here who still uses LaTeX (everyone uses MS Word), so I didn't agitate for an upgrade. Well, I finally got an upgrade, but the frickin' problem doesn't seem to be corrected! Can anyone give me a clue as to what the heck is going on here? I am using \documentclass [twocolumn,10pt]{article} When I type "latex -v", I get: TeX (Web2C 7.4.5) 3.14159 kpathsea version 3.4.5 Copyright (C) 1997-2003 D.E. Knuth. Kpathsea is copyright (C) 1997-2003 Free Software Foundation, Inc. There is NO warranty. Redistribution of this software is covered by the terms of both the TeX copyright and the GNU General Public License. For more information about these matters, see the files named COPYING and the TeX source. Primary author of TeX: D.E. Knuth. Kpathsea written by Karl Berry and others. Russ wrote: > I use LaTeX on Solaris. For years now I've had an ...

recode 1 variable into 2 variables to get an frequency table with unique values
Hi SPSS-Pros, my task is very simple, still I can't figure out how to get it done... I've got 1 string variable listing different names, and the names appear several times. what i want is a typical frequency table of this, listing the unique values (names) and their count, but not in the output but as 2 distinct new variables. is there any command to do that? (i've already tried a lot...) it seems to be more complicated than i thought.. Thanks a lot! Fabian f.floeck@gmail.com wrote: > Hi SPSS-Pros, > > my task is very simple, still I can't figure out how to get it done... > I've got 1 string variable listing different names, and the names > appear several times. > > what i want is a typical frequency table of this, listing the unique > values (names) and their count, but not in the output but as 2 > distinct new variables. is there any command to do that? (i've already > tried a lot...) it seems to be more complicated than i thought.. > > Thanks a lot! > > Fabian Have you tried AGGREGATE? One of the functions returns a count of the number of cases for each unique value of the "break" variable. -- Bruce Weaver bweaver@lakeheadu.ca www.angelfire.com/wv/bwhomedir "When all else fails, RTFM." You could do an automatic recode: Transform -> Automatic recode then select your string variable as the input and give the new variable a name. Ru...

Need to replace missing values with zeros in a varying number of rows and varying number of columns
People, I am working in PC SAS v8.1 I have a program that finds the number of times a varying number of people get a document to different levels of completeness. The program displays this information from the beginning of the current month to the current day. Using a separate column for each day. So as the month continues the number of days(columns) increases. If person A does not get a document to level 2 completeness on a certain day the field currently is blank. The user would like the report to display zeros instead of blanks. I create the report in SAS and then export the final repo...

variable number of variables
Hi guys, I use the following newcommand to ease my work writing vectors and matrices. The example is for a 2x1 matrix (vector). When I use this principle for a 4x4 matrix I get the error message that I ``exceed 12 variables''. Is there a way to make a newcommand using a variable number of variables so I can use it for any size matrix? \newcommand{\vecte}[2] {\ensuremath{ \left(% \begin{array}{c} #1 \\ #2 \\ \end{array}% \right) }} thanks in advance, Peter On Sat, 14 Jan 2006 10:51:15 +0100, Bouwman wrote: > Hi guys, > I use the following newcommand to ease my work writing vectors and > matrices. The example is for a 2x1 matrix (vector). When I use this > principle for a 4x4 matrix I get the error message that I ``exceed 12 > variables''. > Is there a way to make a newcommand using a variable number of variables > so I can use it for any size matrix? > > \newcommand{\vecte}[2] > {\ensuremath{ > \left(% > \begin{array}{c} > #1 \\ > #2 \\ > \end{array}% > \right) > }} > > thanks in advance, > Peter You should not think of commands as functions manipulating their arguments. I principle, your problem can be solved defining a command with a scanning loop. But this has already been done. Plain TeX has \pmatrix{ a & b \cr c & d\cr} etc. The amsmath package for LaTeX has also many solutions. Regards, Bas ...

recoding variables with missing values
Hi. I have a question regarding the recoding of variables with missing values. I have a dateset consisting of a two variables both taking the value one or zero. I then want to create a third variable that takes the value one if both of the original variables have the value one. If one or both of the original variables take the value zero then the new variable takes the value zero and if one of the original variables have a missing value I want the new variables to be a missing value. I use this code to achieve this and it does the trick: if aa708d=. and aa666d=. then extra=.; if aa708d=. and...

Web resources about - how to output number of missing values of a text variable on a table? - comp.soft-sys.sas

Variable - Wikipedia, the free encyclopedia
Text is available under the Creative Commons Attribution-ShareAlike License ;additional terms may apply. By using this site, you agree to the ...

Westpac interest rate rise is one of many variable loan rate rises
WESTPAC is not alone in hitting its owner-occupier customers with a surprise interest rate rise, as dozens of other variable rate home loans ...

Airbnb upgrades variable pricing tool for hosts
Online home rental group Airbnb is rolling out an upgraded variable pricing tool that automatically raises or cuts room rates based on supply ...

Commonwealth Bank raises variable home loan rates
Home owners' mortgage costs are set to rise by hundreds of dollars a year, after the Commonwealth Bank became the latest bank to hike interest ...

CBA lifts variable home loan rates to offset costs associated with stricter capital requirements
The Commonwealth Bank to lift its home loan interest rates independently of the Reserve Bank to partially offset costs associated with stricter ...

Washington Square Signs Spielbergs, Choi Joins Variable and More
... films and an episode of the HBO comedy series "Girls," on which he plays the role of Ray Ploshansky. New York-based creative collective Variable ...

Mozilla moves Firefox to variable release schedule
... schedule process "carefully" and learning "a lot" from it in the past years, Mozilla has announced that Firefox is now moving to a variable ...

"You have to ignore many variables to think women are paid less than men. California is happy to try." ...
Writes Sarah Ketterer in The Wall Street Journal in "The ‘Wage Gap’ Myth That Won’t Die" (which you can get to without subscribing if you Google ...

Staley: 'A lot of different variables...been a hard year'
... 41° Navigation Home Giants A's Sharks Warriors Kings 49ers Raiders Quakes Insiders More Tickets Shop Watch Staley: 'A lot of different variables...been ...

When your environment variables are showing
There are embarrassing moments in life. There is the teacher calling you out as a child in class as you stare out the window wistfully imagining ...

Resources last updated: 3/16/2016 5:41:33 PM