About Cheesiepoof05

Cheesiepoof05 · ‎08-09-2023

Thank you again, but I am not following the prompt piece. This is the code where it was working to create my limit, but I have created a prompt so that when I kick off the query it pops up and I would enter whatever start date I want instead of 01JAN2022. AND datepart(t1.Date) >= '01JAN2022'D AND datepart(t1.Date) <= '31MAR2022'D My prompt is called Start_Date and I updated the code to this: AND datepart(t1.Date) >= (&Start_Date.) AND datepart(t1.Date) <= '31MAR2022'D You provided this but I'm not sure how to use it: %let start_date=%sysevalf('01JAN2022'd);

Cheesiepoof05 · ‎08-09-2023

Thank you, that works perfectly using the dates. I have two follow up questions though. I'm setting these dates up to be prompts and am now struggling to get it to accept the value '01JAN2022'D as the start_date prompt. AND datepart(t1.Date) >= (&Start_Date.) AND datepart(t1.Date) <= '31MAR2022'D Also, the original reason I wanted to break it down to year and month is because I would like to be able to group results by year month to see variances by each month. How is that accomplished if I don't convert the date to something like YYYYMM?

Cheesiepoof05 · ‎08-09-2023

Hello, I'm new to SAS and working on a simple query using a PROC SQL statement. I'm trying to do two things. 1. Convert the date column to a date like 20230101 (also interested in how to convert it to just year month like 202301). 2. I will also need to be able to limit to a certain date range like something like WHERE t1.Date > 20230101 AND t1.Date < 20230331 SAS is currently showing the date in the following format when running my query: 01AUG2017:00:00:00.000 SQL server management studio states that the date format is "datetime" and shows it in this format: 2020-07-07 00:00:00.000 This is my current statement. But I'm looking to convert the date to YYYYMMDD and I also need to add in the ability to limit within a date range. PROC SQL; CREATE TABLE WORK.QUERY_FOR_DATE AS SELECT t1.Event, t1.Date FROM FACS.Occurances t1 ; QUIT;

Cheesiepoof05 · ‎01-23-2023

Thank you.

Cheesiepoof05 · ‎01-23-2023

I got it figured out. I appreciate everyone's time. I went with: substr(cat(adjudication_dt_id),1,4) as Year, substr(cat(adjudication_dt_id),5,2) as Month, Case WHEN CALCULATED Month = '01' THEN "Q1" WHEN CALCULATED Month = '02' THEN "Q1" WHEN CALCULATED Month = '03' THEN "Q1" WHEN CALCULATED Month = '04' THEN "Q2" WHEN CALCULATED Month = '05' THEN "Q2" WHEN CALCULATED Month = '06' THEN "Q2" WHEN CALCULATED Month = '07' THEN "Q3" WHEN CALCULATED Month = '08' THEN "Q3" WHEN CALCULATED Month = '09' THEN "Q3" WHEN CALCULATED Month = '10' THEN "Q4" WHEN CALCULATED Month = '11' THEN "Q4" WHEN CALCULATED Month = '12' THEN "Q4" ELSE "ERROR" END AS Quarter, catx('',CALCULATED Year, CALCULATED Quarter) AS YEAR_QTR,

Cheesiepoof05 · ‎01-23-2023

This is the code I'm using. It's just hitting against a temporary table where I've already pulled the data from. "adjudication_dt_id" is a "numeric" field with 8 digits. I'm just wanting to create a second field where it takes that numeric field and makes it yyyyQq. So 20220418 in the first field would be 2022Q2 in the second field. Proc sql; create table DATE_TEST as Select adjudication_dt_id From SummaryLookup; quit; adjudication_dt_id 20190105 20210907 20200430 20221231

Cheesiepoof05 · ‎01-20-2023

Thank you but I'm not sure how to put that into my Proc sql; code.

Cheesiepoof05 · ‎01-20-2023

Hello, I have a date formatted as "Numeric" like YYYYMMDD ie 20220927. I would like to convert that to Year and Quarter such as 2022Q3. I'm using a Proc sql statement to hit against another table (SummaryLookup) to bring back the date and need to add a line to create a second field to convert it to yyyyQq please. Proc sql; create table DATE_TEST as Select dt_id_YYYYMMDD, From SummaryLookup; quit;

Cheesiepoof05 · ‎09-28-2022

@PaigeMiller Even though this report is the last step of my process? I understand if I was building a database, but I'm just creating a quarterly report.

Cheesiepoof05 · ‎09-28-2022

@PaigeMiller Thanks for the suggestion. Unfortunately my data is in two different locations for the prior quarter and current quarter so I didn't split them apart, rather they were brought in from separate sources. Any way to modify your PROC REPORT to my current format?

Cheesiepoof05 · ‎09-28-2022

I'm new to using SAS Enterprise Guide and am trying to learn how to summarize data and wasn't sure whether code or some SAS EG report is the best method. See below for my current data as well as the results I'm trying to get afterwards. Thank you for your help. I have the following data: CATEGORY NAME STATUS COUNT_2021 MARKET_SHARE_2021 COUNT_2022 MARKET_SHARE_2022 ANIMALS DOG REAL 5 20.00% 28 1.17% ANIMALS DOG IMAGINARY 17 68.00% 2352 98.66% ANIMALS FISH REAL . . . . ANIMALS BEAR REAL 3 12.00% 4 0.17% COLORS GREEN IMAGINARY 8 25.00% 7 0.18% COLORS ORANGE REAL . . . . COLORS BLACK IMAGINARY 24 75.00% 3885 99.82% SHAPES CIRCLE REAL 19 79.17% 27 100.00% SHAPES TRIANGLE IMAGINARY 5 20.83% . . SHAPES SQUARE IMAGINARY . . . . I'm trying to add subtotal lines that break at Category only and sum the counts and market shares such as: (red lines are what I'm seeking to add) CATEGORY NAME STATUS COUNT_2021 MARKET_SHARE_2021 COUNT_2022 MARKET_SHARE_2022 ANIMALS DOG REAL 5 20.00% 28 1.17% ANIMALS DOG IMAGINARY 17 68.00% 2352 98.66% ANIMALS FISH REAL . . . . ANIMALS BEAR REAL 3 12.00% 4 0.17% ANIMALS TOTAL 25 100.00% 2384 100.00% COLORS GREEN IMAGINARY 8 25.00% 7 0.18% COLORS ORANGE REAL . . . . COLORS BLACK IMAGINARY 24 75.00% 3885 99.82% COLORS TOTAL 32 100.00% 3892 100.00% SHAPES CIRCLE REAL 19 79.17% 27 100.00% SHAPES TRIANGLE IMAGINARY 5 20.83% . . SHAPES SQUARE IMAGINARY . . . . SHAPES TOTAL 24 100.00% 27 100.00%

Cheesiepoof05 · ‎09-23-2022

Thank you

Cheesiepoof05 · ‎09-23-2022

I'm using option 1 and just realized I'm getting duplicates if there was more than one of the same color. IE for bears, I'm getting two instances of black in the list. Bears Black, Black, Brown, Red

Cheesiepoof05 · ‎09-23-2022

Thank you for the quick solution. It's working exactly as I need. Sorry about not including the data properly. This was my first question and I thought it was be cleaner for people to see with the formatting but now I know for the future. Thanks again!

Cheesiepoof05 · ‎09-23-2022

I'm using SAS Enterprise guide and switching over from Hyperion Interactive Reporting. I have two data columns that I'm trying to create a summary column of all the unique responses from another column. In the example below I am trying to create the "Colors_By_Animal" column where all of the unique options of colors per animal are rolled together and separated by commas in a third column. Duplicate colors per animal are only shown once and blanks are ignored. Either option of adding the column in with all the data or creating a summary table with just one line per animal would work for me. Any ideas? Please and Thank you.

Online Status	Offline
Date Last Visited	‎01-12-2024 08:00 PM

Retaining only the lowest price per item number

Re: Grouping by Date Formatted as Year Quarter

Re: Grouping by Date Formatted as Year Quarter

Grouping by Date Formatted as Year Quarter

Re: Sum a case statement

Sum a case statement

Re: Date Where Statement Syntax

Re: Date Where Statement Syntax

Date Where Statement Syntax

Re: Pulling back most recent result per another field

Re: Date Where Statement Syntax

Re: Date Where Statement Syntax

Re: Date Where Statement Syntax

Re: Pulling back most recent result per another field

Re: Pulling back most recent result per another field

Re: Summary Column for Text Data

Re: Summary Column for Text Data

Re: Convert Dates and Setting Date Limits

Re: Convert Dates and Setting Date Limits

Convert Dates and Setting Date Limits

Re: Turn YYYYMMDD date into Year Qtr yyyyQq

Re: Turn YYYYMMDD date into Year Qtr yyyyQq

Re: Turn YYYYMMDD date into Year Qtr yyyyQq

Re: Turn YYYYMMDD date into Year Qtr yyyyQq

Turn YYYYMMDD date into Year Qtr yyyyQq

Re: Creating Subtotals Based on one column

Re: Creating Subtotals Based on one column

Creating Subtotals Based on one column

Re: Summary Column for Text Data

Re: Summary Column for Text Data

Re: Summary Column for Text Data

Summary Column for Text Data