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

I have data with all 4 quarter data however when i do Proc Expand and convert from Qtr to Month its only giving up to October data . 

New_date is formated as YYq.

 

proc expand data=MS_score1 out=MS_score from=qtr to=month;
convert MDHCP;
by RID FullName;
id new_date;
run;

 

how to get the other two months 

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

The problem  is in the CONVERT statement, namely the "/observed=..." parameter.  When absent, this parameter defaults to "beginning", as in

     convert mdhcp /observed=(beginning,beginning);

 

which tells expand that both the incoming data and the outgoing data represent the "beginning" of the interval.  So for from=qtr to=month it means that the last outgoing item is for the beginning of the interval, namely october for quarter 4.

 

try something like

   convert mdhcp/observed=(total,total);

 

which should divide up each  MDHCP into 3 almost-equal amounts. I say  "almost equal" because the number of days in various months are "almost  equal".

 

Another option

 convert mdhcp/observed=(average,average);

 

which would work if mchcp is a  daily rate, recorded over a month, and you want daily rates for months.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

3 REPLIES 3
Reeza
Super User
For a problem like this we also need sample data to replicate your issue.
hk2013
Fluorite | Level 6

here is a example of the data 

name   qtr     score 

JUNG 2017Q1 38
JUNG 2017Q2 19
JUNG 2017Q3 27
JUNG 2017Q4 33
PHAM 2017Q1 17
PHAM 2017Q2 17
PHAM 2017Q3 15
PHAM 2017Q4 16

 

and the out comes out to be and its missing NOv and dec

 

JUNGJAN2017
JUNG FEB2017
JUNG MAR2017
JUNG APR2017
JUNG MAY2017
JUNG JUN2017
JUNGJUL2017
JUNGAUG2017
JUNG SEP2017
JUNG OCT2017
PHAMJAN2017
PHAMFEB2017
PHAMMAR2017
PHAM APR2017
PHAMMAY2017
PHAMJUN2017
PHAMJUL2017
PHAM AUG2017
PHAM SEP2017
PHAM OCT2017

 

mkeintz
PROC Star

The problem  is in the CONVERT statement, namely the "/observed=..." parameter.  When absent, this parameter defaults to "beginning", as in

     convert mdhcp /observed=(beginning,beginning);

 

which tells expand that both the incoming data and the outgoing data represent the "beginning" of the interval.  So for from=qtr to=month it means that the last outgoing item is for the beginning of the interval, namely october for quarter 4.

 

try something like

   convert mdhcp/observed=(total,total);

 

which should divide up each  MDHCP into 3 almost-equal amounts. I say  "almost equal" because the number of days in various months are "almost  equal".

 

Another option

 convert mdhcp/observed=(average,average);

 

which would work if mchcp is a  daily rate, recorded over a month, and you want daily rates for months.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

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
  • 3 replies
  • 952 views
  • 0 likes
  • 3 in conversation