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

I have the following dataset and what I want to do is listed below:

 

data have;
 infile cards truncover expandtabs;
 input MC $ ET $ Date :date9. Time :time. PMC $ PXMC $ Site $ Dia MV SF;
 format date date8. Time HHMM.;
 cards;
US000409 Meas 12Nov2016 17:26 7101 Et1 1 . 2780462.00000 1
US000409 Meas 12Nov2016 17:33 7101 Et1 1 861.26 2780462.00000 1
US000409 Lath 12Nov2016 17:33 7102 Et1 1 861.6 2780462.00000 1
US01036 Meas 12Nov2016 17:26 7101 Et1 2 . 522860.00000 1
US01036 Lath 13Nov2016 17:33 7202 Et1 2 866.68 522860.00000 1
US01036 Meas 13Nov2016 17:33 7101 Et1 2 867.36 522860.00000 1
US02044 Meas 13Nov2016 17:26 7202 Et1 1 . 569298.00000 1
US02044 Lath 13Nov2016 17:33 7202 Et1 1 865.32 569298.00000 1
US02044 Meas 14Nov2016 17:33 7202 Et1 2 865.68 569298.00000 1
US318 Lath 14Nov2016 17:26 7101 Et2 2 . 2630856.00000 1
US318 Meas 14Nov2016 17:33 7202 Et2 3 863.26 2630856.00000 1
US318 Lath 14Nov2016 17:33 7202 Et2 3 863.94 2630856.00000 1
US000409 Meas 15Nov2016 21:56 7202 Et2 3 860.98 2780462.00000 1
US000409 Meas 15Nov2016 21:56 7203 Et2 4 861.5 2780462.00000 1
US01036 Lath 16Nov2016 21:56 7101 Et2 4 866.64 522860.00000 1
US01036 Meas 16Nov2016 21:56 7202 Et2 4 867.34 522860.00000 1
US02044 Lath 17Nov2016 21:56 7203 Et2 1 865.3 569298.00000 1
US02044 Meas 17Nov2016 21:56 7204 Et2 3 865.68 569298.00000 1
US318 Lath 17Nov2016 21:56 7204 Et2 2 863.24 2630856.00000 1
;
run;

 

I want to compuet:

 

1. Daily Mean of 'Dia' for each 'MC'

2. For each 'PMC', step 1 above

3. For each 'MC', what is the difference between 'MV'

4. For each Year in 'Date', how many conts 'Lath' are there for each 'MC'

 

 

Please help me learning SAS. Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, look at the code you have written versus the code I provided.  You will note that you both select, and group by a variable called year, which is in fact a date, applying a format does not change the underlying data, only how it is displayed.  So behind the scenes you are grouping your data based on the full data.  Why did you not use the year() function as I provided above?

Oh, and @imanojkumar1 is correct, the functions are year() not yearpart():

proc sql;
  create table WANT as
  select  year(DATE) as YR,
          count(ET) as RESULT
  from    HAVE  
  group by year(DATE);
quit;

View solution in original post

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well (note, make sure dataset is sorted)

1)  

proc means ...;

  by mc date;

...

run;

 

2)

proc means ...;

  by pmc date;

...

run;

 

3)

Not sure what you mean, do you mean difference between largest, and smallest, first/last, sum them all - first?

 

4)

proc sql;

  create table WANT as

  select  yearpart(DATE) as YR,

            count(ET) as RESULT

  from   HAVE  

  group by yearpart(DATE);

quit;

 

The above have many examples in the SAS documentation, and on here.

 

Do also note, you could do all of the given problems in one datastep, use retain variables, and set your by groups, but maybe best to learn to do each bit before jumping in.

imanojkumar1
Quartz | Level 8

Thank you so much.

 

in 3).  I means MV2 - MV1 i.e. current - lag value (but datewise)

 

in 2).  Daily Mean of 'Dia' for each 'MC' grouped by

 

I appreciate your advice. I just started learning SAS and miles to go. 🙂

ballardw
Super User

@imanojkumar1 wrote:

Thank you so much.

 

in 3).  I means MV2 - MV1 i.e. current - lag value (but datewise)

 

 


If you want to calculate the current record difference from the previous record for the same variable SAS provides a function DIF to do that.

However do you want that different only with the same date (from your datewise comment) or the max difference between two days, the range for a given date (maximum - minimum)  or something else? And do you what that comparison to go across ET? Is the data allowed to be sorted so that all of MC are together?

 

Another fundamental question is also do you expect a data set as the result or a report for people to read? It may help a bit to show what you would expect to see from your given example data.

 

 

 

imanojkumar1
Quartz | Level 8

yes Sorting is possible on all the variable except for SF. Thanks for the advice.

 

Could you please let me know how DIF can be used here?

imanojkumar1
Quartz | Level 8

I think is it...

 

proc sql;
  create table WANT as
  select  year(DATE) as YR,
            count(ET) as RESULT
  from   HAVE  
  group by year(DATE);
quit;


*For timestamp it will be year(datepart(datetimestamp));
imanojkumar1
Quartz | Level 8

proc sql;
create table WANT as
select year(DATE) as YR,
count(ET) as RESULT
from HAVE
group by year(DATE);
quit;

does not work 😞

in the result there are 19 rows and each rows has 2016 and 19 as YR and Result.

 

2016 19
2016 19
2016 19
2016 19
2016 19
. .
. .

 

 

Then,

 

I tried this:

 

PROC SQL;
   CREATE TABLE HAVE01 AS 
   SELECT t1.*, t1.Date FORMAT=YEAR4. LABEL="Year" AS Year 
            FROM HAVE t1;
 QUIT;

PROC SQL;
   CREATE TABLE WANT AS 
   SELECT Year, (COUNT(ET)) AS COUNT_of_ET
      FROM HAVE01
      GROUP BY Year;
QUIT;

 

And expected somthing like this:

 

Year    Count_of_ET
2015 4 2016 15

 

But got like this:

 

Year    Count_of_ET
2015 2 2015 1 2015 1 2016 1 2016 2 2016 1 2016 4 2016 2 2016 2 2016 3

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, look at the code you have written versus the code I provided.  You will note that you both select, and group by a variable called year, which is in fact a date, applying a format does not change the underlying data, only how it is displayed.  So behind the scenes you are grouping your data based on the full data.  Why did you not use the year() function as I provided above?

Oh, and @imanojkumar1 is correct, the functions are year() not yearpart():

proc sql;
  create table WANT as
  select  year(DATE) as YR,
          count(ET) as RESULT
  from    HAVE  
  group by year(DATE);
quit;

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 7 replies
  • 968 views
  • 2 likes
  • 3 in conversation