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

Experts,

Below is the output result of proc compare which compares two data sets and tells whether observations have matching values for variables. It the values are matching it gives the difference as 0 otherwise the calculated value.

Now I want to limit this data to only those "C_name" for which all "_OBS_" are having "Value"=0 .


Kindly see c_name "onn" is having one zero for its set of data and I dont want data relating to ONN to be in my new dataset. I only want those C_name for which all obs have 0 values; so the condition need to check whether the "value" is 0 for all the unique "C_names" .


One more thing the _OBS_ reference may change i.e here I have posted only 6 but originally this number may be huge so its better if we have something which is dynamic and automatically COUNTS the number of _OBS_ for unique c_names which will be same for all c_name.

_TYPE__OBS_C_namemetricvalue
DIF1heroar_y188
DIF2heroar_y2-22
DIF3heroar_y3-22
DIF4herocash_y179
DIF5herocash_y2-10
DIF6herocash_y3-10
DIF1onnar_y10
DIF2onnar_y2-20
DIF3onnar_y3-20
DIF4onncash_y10
DIF5onncash_y2-22
DIF6onncash_y375
DIF1yuar_y10
DIF2yuar_y20
DIF3yuar_y30
DIF4yucash_y10
DIF5yucash_y20
DIF6yucash_y30

Requirement in NUTSHELL:

ALL the C_names which have all the values zero for all the obs (here we have only one C_name which fits our criteria  and that is "YU").

Thanks a lot...!

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

or, possibly even better yet:

proc sql;

  create table nullNames as

    select c_name

      from have

        group by c_name

          having min(value)=0 and max(value)=0 and nmiss(value)=0

  ;

quit;

View solution in original post

9 REPLIES 9
art297
Opal | Level 21

Of course there is a function for that, but you have to use proc sql. e.g.:

proc sql;

  select distinct c_name

    from have

      group by c_name

        having sum(value) eq 0

  ;

quit;

PGStats
Opal | Level 21

You are better off with:

proc sql;

create table nullNames as

select c_name

from have

group by c_name

having min(value)=0 and max(value)=0;

quit;

PG

PG
art297
Opal | Level 21

or, possibly even better yet:

proc sql;

  create table nullNames as

    select c_name

      from have

        group by c_name

          having min(value)=0 and max(value)=0 and nmiss(value)=0

  ;

quit;

Reeza
Super User

That's a bad subject line.

There's a way to do it, you don't know how, big difference.

HarshadMadhamshettiwar
Obsidian | Level 7

Apology for that but it is partially true if we seek some datastep functions to the job.

Nevertheless thanks everyone for motivating to participate and correcting newbies where we go wrong.

PGStats
Opal | Level 21

Or, if your dataset is already sorted by c_name:

data nullNames(keep=c_name);

do until(last.c_name);

  set have; by c_name;

  if value ne 0 then flag=1;

  end;

if not flag then output;

drop flag;

run;

PG

PG
Reeza
Super User

I'd use a different approach all together, using the outstate dataset from proc compare which lists all variables that are equal in both data sets and then isolate the one where the NDIF=0.

Proc Compare also has listequalvars option that will list the variables that are equal though that's partially useless since you can't capture it in a table.

I think SAS should enable a way to capture that information directly

https://communities.sas.com/ideas/1734

data class;

set sashelp.class;

  if age=13 then weight=weight+3;

  if age=16 then BMI=weight*height;

run;

proc compare data=sashelp.class compare=class outstats=part1 listequalvars;

run;

data want;

  set part1;

  where _type_='NDIF' and _base_=0 and _comp_=0;

  keep _var_;

run;


Sample output:


                                 Variables with All Equal Values

                                  Variable  Type  Len1 Len2

                                  Name      CHAR     8    8

                                  Sex       CHAR     1   20

                                  Age       NUM      8    8

                                  Height    NUM      8    8

                                 Variables with Unequal Values

                           Variable  Type  Len1 Len2  Ndif   MaxDif

                           Weight    NUM      8    8     3    3.000

Ksharp
Super User
data have;
input _TYPE_ $     _OBS_     C_name     $ metric $ value ;
cards;
DIF     1     hero     ar_y1     88
DIF     2     hero     ar_y2     -22
DIF     3     hero     ar_y3     -22
DIF     4     hero     cash_y1     79
DIF     5     hero     cash_y2     -10
DIF     6     hero     cash_y3     -10
DIF     1     onn     ar_y1     0
DIF     2     onn     ar_y2     -20
DIF     3     onn     ar_y3     -20
DIF     4     onn     cash_y1     0
DIF     5     onn     cash_y2     -22
DIF     6     onn     cash_y3     75
DIF     1     yu     ar_y1     0
DIF     2     yu     ar_y2     0
DIF     3     yu     ar_y3     0
DIF     4     yu     cash_y1     0
DIF     5     yu     cash_y2     0
DIF     6     yu     cash_y3     0
;
run;

proc sql;
  create table nullNames as
    select c_name
      from have
        group by c_name
          having sum(value ne 0)=0
  ;
quit;

Xia Keshan

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
  • 9 replies
  • 1307 views
  • 8 likes
  • 5 in conversation