topic Re: Delete variables from cell in New SAS User

Delete variables from cell

hk2013 — Fri, 12 Oct 2018 20:43:21 GMT

I have a data set were few variables are repeating but i only want to keep info for a specific variable

Have:

Name ID Code code_description type

Sarah 0007 A19 DM test mining

Sarah 0007 B45 DM test list

Jack 1111 F56 HPY type 1 mining

Jack 1111 H78 HPY type 1 list

Want

Name ID Code code_description type

.. .. A19 DM test mining

Sarah 0007 B45 DM test list

.. ... F56 HPY type 1 mining

Jack 1111 H78 HPY type 1 list

so i want to delete the name and ID or just place a zero or none for mining when that data is repeated like this.

Re: Delete variables from cell

novinosrin — Fri, 12 Oct 2018 20:59:58 GMT

Repeating , ok. How many repeating or is it just 2 like in your sample? Which record within a by group do you want to keep(some logic for that) plz?

Re: Delete variables from cell

hk2013 — Fri, 12 Oct 2018 21:12:21 GMT

its will always be just 2 repeat instances like the sample. I want to keep the info for the 'list' type only and remove info for the 'mining' type

Re: Delete variables from cell

novinosrin — Fri, 12 Oct 2018 21:16:52 GMT

data have;
input Name   $   ID      $     Code  $     code_description  & $15.  type $;
cards;
Sarah      0007       A19            DM test                mining
Sarah      0007       B45            DM test                 list 
Jack        1111        F56            HPY type 1            mining
Jack        1111        H78            HPY type 1            list 
;


data want;
set have;
if type='mining' then call missing(name,id);
run;


data want;
set have;
if type ne 'list' then call missing(name,id);
run;

Re: Delete variables from cell

hk2013 — Fri, 12 Oct 2018 21:20:20 GMT

thank you replying but i have other 'mining' type data in my set and they dont have 'list' type repeating for them so when that happens i want to keep the info as it is

Re: Delete variables from cell

novinosrin — Fri, 12 Oct 2018 21:28:32 GMT

Does that mean those others have just 1 record for an id? or is it 2 records both with type='mining' ?

Can you modify the sample

Re: Delete variables from cell

hk2013 — Fri, 12 Oct 2018 21:35:39 GMT

data have;
input Name   $   ID      $     Code  $     code_description  & $15.  type $;
cards;
Sarah      0007       A19            DM test                mining
Sarah      0007       B45            DM test                 list 
Jack        1111        F56            HPY type 1            mining
Jack        1111        H78            HPY type 1            list 
Bob         1222       F56             HPY type 1            mining
Rob         3333       A19            DM test                  mining
;

so some ids only have mining type but i dont wwant to remove their info. I only want to remover mining info if they have list type and mining type

Re: Delete variables from cell

novinosrin — Fri, 12 Oct 2018 21:36:48 GMT

data have;
input Name   $   ID      $     Code  $     code_description  & $15.  type $;
cards;
Sarah      0007       A19            DM test                mining
Sarah      0007       B45            DM test                 list 
Jack        1111        F56            HPY type 1            mining
Jack        1111        H78            HPY type 1            list 
Bob         1222       F56             HPY type 1            mining
Rob         3333       A19            DM test                  mining
;
proc sort data=have;
by id;
run;
data want;
call missing(f);
do until(last.id);
set have;
by id;
if type='list' then f=1;
end;
do until(last.id);
set have;
by id;
if f and type='mining' then call missing(name,id);
output;
end;
drop f;
run;

Re: Delete variables from cell

Reeza — Fri, 12 Oct 2018 23:59:00 GMT

Would it make sense to collapse the data, one record per person, with two columns for mining/list.

Re: Delete variables from cell

Tom — Sat, 13 Oct 2018 19:38:21 GMT

It sounds like that you have multiple observations per NAME,ID group and when that happens you want to remove the NAME and ID values from the observations that has TYPE='mining' and keep them for the observations that have TYPE='list'.

This sounds like a bad idea. What is the reason you want to do this? Perhaps you are just trying to produce a report?

If so then perhaps it is better to leave the values in place and use a reporting method that display the id variables just once per group?

data have ;
  input Name $ ID $ Code $ code_description &:$15. type $ ;
cards;
Sarah  0007 A19 DM test     mining
Sarah  0007 B45 DM test     list 
Jack   1111 F56 HPY type 1  mining
Jack   1111 H78 HPY type 1  list
;

proc sort; by Name ID type ; run;
proc print ;
  by Name Id;
  id Name Id;
run;

                            code_
Name      ID     Code    description     type

Jack     1111    H78     HPY type 1     list
                 F56     HPY type 1     mining


Sarah    0007    B45     DM test        list
                 A19     DM test        mining