Hello everyone, I was fortunate enough to get some help yesterday here and again thanks to the two members. My dataset has a number of faulty registrations that I need to address and I hope you guys can help out. I’m trying to calculate the total number of days each per_id stays at a given inst_id. I have tried to illustrate the four main issues. Issue 1 : A stay for the same person is registered at an institution B during a stay at institution A (row 1 and 2). Row 2 should be removed entirely. Issue 2. A stay at an institution B starts during a stay at inst A and ends after moveout date for inst A. The movein date at inst B should be rectified to moveout date at inst A. Issue 3: sort of the reverse to issue 2. The two separate stays at inst c should be put together to one long stay. Issue 4: this is a special case where the person moves in and out on the same date. I would like to keep these, unless they are during the middle of a different stay (like issue 1) It’s quite a mouthful but the end goal is a tidy data set with one row per person per institution per period. Any help is greatly appreciated. Inst_id Per_id bbMovein Moveout A A. 01JAN2022 10JAN2022 B. A. 03JAN2022 07JAN2022 A. B. 04JAN2022 08JAN2022 B. B. 07JAN2022. 11JAN2022 C. C. 05JAN2022. 08JAN2022 C. C. 08JAN2022. 09JAN200 E. D. 02JAN2022 02JAN2022
... View more
Hello, I have several sas tables (T1, T2, T3, etc.) I want to concatenate its different tables.
I do the data step correctly with the set instruction. But I get empty lines. On the documentation, it states to see the types of certain variables and to order the variables. I've done it but I still get empty observations. I cannot send an extract of the bases because I cannot find the examples that I know how to create to reflect the problem. Thank you for understanding.
Can anyone give me some hints ?
... View more
Hi! I'm trying to write a macro that will pick a base mean and compare all the other means in my dataset to that mean (by doing a t-test). I also want to compare all the means to the international mean. I want my ouput to be a table that has the country ID (IDCNTRY), the mean (mnpv), the standard error (mnpv_se), the t value, and the label for 'H' or 'L' to indicate if the means are significantly higher or lower. Here is what I have but I keep getting errors: %MACRO T(YEAR=, FILE=, base=, scale_name=, TABLE=); %put TABLE: &TABLE.; ODS SELECT NONE; proc sql; select mnpv, mnpv_se into :mean_base, :mean_se_base from OUTDIR.&FILE._&TABLE. where IDCNTRY="&base"; quit; proc sql; CREATE TABLE &scale_name. AS SELECT IDCNTRY, mnpv AS &scale_name._Mean, mnpv_se AS &scale_name._SE, mnpv_se * mnpv_se AS &scale_name._SE_Square, /* international average */ (mnpv - 496) / mnpv_se AS &scale_name._t_500, ((mnpv - 496) / mnpv_se ) / 1.96 AS &scale_name._t_cv_500, CASE WHEN calculated &scale_name._t_cv_500 >= 1 THEN ' H' WHEN -1 < calculated &scale_name._t_cv_500 < 1 THEN ' ' WHEN calculated &scale_name._t_cv_500 <= -1 THEN ' L' END AS &scale_name._sig_500, /* comparing to base group */ (mnpv - &mean_base.) / sqrt(mnpv_se**2 + &mean_se_base.**2) AS &scale_name._t_base, ((mnpv - &mean_base.) / sqrt(mnpv_se**2 + &mean_se_base.**2)) / 1.96 AS &scale_name._t_cv_base, CASE WHEN calculated &scale_name._t_cv_base >= 1 THEN ' H' WHEN -1 < calculated &scale_name._t_cv_base < 1 THEN ' ' WHEN calculated &scale_name._t_cv_base <= -1 THEN ' L' END AS &scale_name._sig_base FROM OUTDIR.&FILE._&TABLE. ORDER BY IDCNTRY; QUIT; proc sql; CREATE TABLE &scale_name._2 AS SELECT * FROM &scale_name. ORDER BY IDCNTRY, &scale_name._Mean DESC; QUIT; ods select all; PROC PRINT DATA=&scale_name.; TITLE "&TABLE.: &scale_name."; FORMAT _NUMERIC_ 20.5; RUN; %MEND T; /*----------------------------------------------------------*/ /* Table 1. */ %T(YEAR=2018, FILE=D2018, base=USA scale_name= CIL TABLE= TABLE1); What am I missing??
... View more
I am looking for a way to display the distinct count of users based on the user type, in a cross tab, based on grouping displayed as a hierarchy is expanded. A user could be one of two types - and there needs to be a column for each type, for the distinct count of users of that type. Say, Type A, and Type B. Each user has a unique identifier and a type recorded, and can have multiple rows of data for each level of the hierarchy. The hierarchy in the cross tab, is: Financial Year -> Financial Year Quarter -> Residential State When the cross tab displays Financial Year, it is to display distinct count in columns for Type A, and Type B, for the Financial Year. Then, when hierarchy expands to Financial Year Quarter, it displays distinct count of Type A and Type B, for the Financial Year -> Financial Year Quarter. Then, when hierarchy expands to Residential State, it displays distinct count of Type A and Type B, for the Financial Year -> Financial Year Quarter -> Residential State.
... View more
I have several categorical variables like gender, race, etc. where I want to find the mean of each category. For example my variable is race and race=1 means white, race=2 means Black, race=3 means Asian/PI. I want to find the mean number of people in my dataset who are members of each race, as well as the standard deviation. Would I use PROC FREQ for this or PROC MEANS?
... View more
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.