topic Re: How to count characteristics of a variable? in SAS Programming

How to count characteristics of a variable?

marysmith — Tue, 27 Feb 2018 10:52:15 GMT

Dear community,

I have a Character variable "sponsor"s and I need SAS to tell me how many different characters this variable has. How many different sponsors are there?

How can I do it?

Thanks!

Re: How to count characteristics of a variable?

RW9 — Tue, 27 Feb 2018 10:59:28 GMT

You need to present some test data in the form of a datastep and what you want to see output. There is not enough information to guess here. Maybe a proc freq on that variable?

Re: How to count characteristics of a variable?

marysmith — Tue, 27 Feb 2018 11:03:55 GMT

I tried something like that:
proc print
data= saspms.datensatz_1416_Erstanzeige;
sum sponsor;
run;

but it tells me: ERROR: Variable sponsor in list does not match type prescribed for this list.

Re: How to count characteristics of a variable?

RW9 — Tue, 27 Feb 2018 11:05:42 GMT

I am sure I said "proc freq"?

http://support.sas.com/documentation/cdl/en/procstat/66703/HTML/default/viewer.htm#procstat_freq_syntax.htm

Sum on proc print requires numeric variables, yours is character.

Re: How to count characteristics of a variable?

marysmith — Tue, 27 Feb 2018 11:10:55 GMT

Yes thank you I already did that. But it doesn't show me what I need. I need SAS to count the column sponsors. With proc freq I can only see the frequency of the different sponsors. I want SAS to sum the column sponsors. Do you know what I mean?
Thank you 🙂

Re: How to count characteristics of a variable?

RW9 — Tue, 27 Feb 2018 11:29:35 GMT

Well, I can have another guess. Its far simpler if you just provided test data in the form of a datastep, and what you want the output to look like. Maybe:

proc sql;
  create table want as
  select sponser,
         count(*) as total
  from   have
  group by sponser;
quit;

Re: How to count characteristics of a variable?

Astounding — Tue, 27 Feb 2018 12:03:18 GMT

I think you're getting closer. Here's my interpretation:

proc sql;

create table want as

select count(distinct sponsor) as n_sponsors

from have;

quit;

Re: How to count characteristics of a variable?

marysmith — Tue, 27 Feb 2018 14:59:26 GMT

Thank you so much! It worked! Yay! Now I want the number of the different sponsors for each year.

Can I do it with the proc sql command? As sponsor is a characteristic variable with different names I have no idea how to do that.

I created a year variable:

data saspms.datensatz_1416_Erstanzeige;
set saspms.datensatz_1416_Erstanzeige;
format n_date ddmmyy10.;
n_date = input(notificationdate,ddmmyy10.);
if '01jan2013'd <= n_date <= '31dec2013'd then year=2013;
if '01jan2014'd <= n_date <= '31dec2014'd then year=2014;
if '01jan2015'd <= n_date <= '31dec2015'd then year=2015;
if '01jan2016'd <= n_date <= '31dec2016'd then year=2016;
if '01jan2017'd <= n_date <= '31dec2017'd then year=2017;
run;

and the sponsor variable:

data saspms.datensatz_1416_Erstanzeige;
     set saspms.datensatz_1416_Erstanzeige;
     sponsor = Auftraggeber;
     if Auftraggeber = "." then sponsor = Auftraggeber;
     else sponsor = Manufacturer; /*Informationen aus Variable Auftraggeber, falls diese nicht vorhanden, Informationen aus Variable Manufacturer/Institution*/
   label sponsor = "Sponsoren";
run;

Cab abybody help please?:)

Re: How to count characteristics of a variable?

RW9 — Tue, 27 Feb 2018 15:07:47 GMT

Should really open a new question. Just update the group by to the variables you want to group the data by:

  group by sponser n_date;

Re: How to count characteristics of a variable?

marysmith — Tue, 27 Feb 2018 15:51:40 GMT

i tried this: but its not working 😞

proc sql;

create table saspms.sponsoren1 as

select count(distinct sponsor) as n_sponsors

from saspms.datensatz_1416_Erstanzeige;
group by sponser n_date

quit;

SAS tells me:180-322: Statement is not valid or it is used out of proper order.

Re: How to count characteristics of a variable?

RW9 — Tue, 27 Feb 2018 16:03:45 GMT

Drop the semicolon after 1416_Erstanzeige; <- ther should not be a semicolon until the end of the sql clause (in this case before the quit).

Re: How to count characteristics of a variable?

marysmith — Tue, 27 Feb 2018 16:08:55 GMT

Thank you! That was it!

The table I get looks like that:

1 61

2 62

3 59

How do I know which number is which year?

Do you have any guess?

Cheers!

Re: How to count characteristics of a variable?

RW9 — Tue, 27 Feb 2018 16:11:11 GMT

proc sql;
  create table saspms.sponsoren1 as
  select sponsor,
         n_date,
         count(distinct sponsor) as n_sponsors
  from saspms.datensatz_1416_Erstanzeige
  group by sponser,n_date
quit;

Re: How to count characteristics of a variable?

marysmith — Tue, 27 Feb 2018 16:15:55 GMT

Thank you but that's not quite the result I need.

I need SAS to tell me in year2014 there where n different sponsors, in year 2015 there where n different sponsors and in year 2016 there where n different sponsors.
Is that even possible? 🙂

Re: How to count characteristics of a variable?

RW9 — Tue, 27 Feb 2018 16:30:46 GMT

proc sql;
  create table saspms.sponsoren1 as
  select n_date,       
         count(distinct sponsor) as n_sponsors
  from saspms.datensatz_1416_Erstanzeige
  group by n_date
quit;

This would be far easier if you followed the guidance on posting a new question - provide test data in the form of a datastep, show what the output should look like, etc.