Sas EG code

[priyanka14]

Hello , 

I am in urgent need of a SAS EG code. I need the same thing which I asked you earlier but the only thing is like now I added one more category. I have attached the excel sheet also which has input and the output which I required. First of all , I need to sort the category, category2,Month in descending order . For each category , there are four category2 level ( 1,2,3,4). So My requirement is that for each category and its each category2 level , first I need check used column . and in used column , I have to find out the number greater than or equal to 3 and need to select only the continuous rows. Suppose with Category 'A' having Category2 as '4', in "used"column the data are 12,12,2,3,8,10 , then I need to select all rows only from used column data 3 ( 3,8,10) Same thing I have highlighted in" sorted data" that whatever the data is required in output sheet. and if in some day I have one more category after category2 like Category3 and again the I need the same kind of output looking this added category also. then can you suggest me where I need to make changes. In this attached sheet , the input sheet and then after sorting the input sheet by category,category2 and months and then the output sheet ( the way I want my result). I tried my concatenating the category A and category 2 , and sorting by month and then using same code I got from Sas team but in this case it is not working.please suggest me what will be easy way to do. Will some macro useful so that in future if more variables get added like category3 , I won't stuck and can use the same code.

The question and solution of my previous question link:

https://communities.sas.com/t5/New-SAS-User/Need-SAS-code-for-selecting-rows-based-on-condition/m-p/...

Solution

https://communities.sas.com/t5/New-SAS-User/Need-SAS-code-for-selecting-rows-based-on-condition/m-p/...

Hope you get my query.

Thanks

[Reeza]

Show the code you tried.

[priyanka14]

In link mentioned above in my post have both the question which I asked earlier and the code which I got.
Here also I am attaching the code.
The only difference is earlier I have only one category but now I have two category column (category, category 2) category 2 is like the sunset of category one. I guess , it will be more clear if you open the Excel sheet which I attached.there I mentioned the input and the output which I really required.

Here is the code. One modification which I was trying to do is to concatenate category and category2 and named the resultant column as ConCategory and in code wherever Category written , I replaced it with ConCategory but failed to get the output the way I want.


This is the original code which I got from Sas team.

DATA WORK.HAVE;
FORMAT Category $1. Month MonYY5. used 8. new_used 8. variety $3.;
INFORMAT Category $1. Month MonYY5. used 8. new_used 8. variety $3.;
INPUT Category Month used new_used variety;
INFILE DATALINES DLM='|' DSD;
DATALINES;
A|Jan-18|6|20|MI
B|Jan-18|8|20|MI
C|Jan-18|8|20|AZ
D|Jan-18|8|20|AZ
A|Feb-18|5|20|AP
B|Feb-18|12|20|AP
C|Feb-18|23|20|MI
D|Feb-18|23|20|MI
A|Mar-18|5|20|NOK
B|Mar-18|3|20|NOK
C|Mar-18|3|20|AP
D|Mar-18|3|20|AP
A|Apr-18|10|20|AC
B|Apr-18|18|20|AC
C|Apr-18|18|20|NOK
D|Apr-18|18|20|NOK
A|May-18|9|20|PS
B|May-18|5|20|PS
C|May-18|5|20|AC
D|May-18|5|20|AC
A|Jun-18|8|20|CD
B|Jun-18|6|20|CD
C|Jun-18|6|20|PS
D|Jun-18|6|20|PS
A|Jul-18|3|20|MI
B|Jul-18|7|20|MI
C|Jul-18|7|20|CD
A|Aug-18|2|2|EH
B|Aug-18|2|2|EH
C|Aug-18|2|2|MI
D|Aug-18|2|2|CD
A|Sep-18|12|20|EF
B|Sep-18|12|20|EF
C|Sep-18|12|20|EH
D|Sep-18|7|20|MI
A|Oct-18|12|20|AZ
C|Oct-18|10|20|EF
;


PROC SORT DATA=WORK.HAVE; BY Category DESCENDING Month; RUN;


/* ID MAX DATE per Category where Used value LT 3 */
PROC SQL;
CREATE TABLE WORK.WANT_A AS
SELECT
Category
, MAX(Month) AS MAXMONTH FORMAT=MONYY5.

FROM WORK.HAVE
WHERE Used < 3
GROUP BY Category
ORDER BY Category, MAXMONTH;
QUIT;


/* JOIN MAX DATE TABLE BACK TO FULL TABLE */
PROC SQL;
CREATE TABLE WORK.WANT_Final AS
SELECT
a.*

FROM WORK.Have AS a
LEFT JOIN WORK.WANT_A AS b ON a.Category=b.Category
WHERE a.MONTH < b.MAXMONTH;
QUIT;

[Reeza]

I don't see conCategory in your code anywhere, this isn't the code you tried.

[priyanka14]

Hi ,

 Sorry I shared the same code which I got from sas team.

Now here is the code which I tried. I am attaching the new excel so that it can help you to understand my actual requirement.

A bit change with the requirement. I am trying to write down what extactly I am looking for.

In the Excel sheet , Input tab for category F2, Category 4, I would like to select the continuous 6 rows from P_mnth1 201808. The logic is like I have to first check in column " Time" for each category and its category2 where Time is equal to or greater than 3. From there I have to select 6 rows continuously but if in case Time is "." as p_count is 0 and next row having "Time column"  3 orgreater than 3, then I need to take 6 rows from where time column".". Like in Category F2, Category2 as 4 , I want rows from 082018 as P_count is 0 and next row below it have time 3. Like now for categoryF2 with category2 as 3, I will select rows from 032018.

This what modification I have from earlier query . earlier I was looking the continuous rows where time column is 3 or greater 3 starts . I was not including the row where time column is ".". for ex earlier , with same category F2, Category2 as 4 , I was looking rows from 072018.

Hope this helps in understanding as its difficult to explain in writing all.

The code over which I was trying is earlier one only so for  selecting Continous 6  rows from time 3 or greater than 3  .

Request you to please look at the excel attached. The input and output which I really want.

Here is the code which I was trying .

PROC SQL;

Create Table NEW As

Select Distinct

Category,

Category2,

P_mnth1,

PCount,

Time,

(CAT(Category,Category2)) As New Category

From SAS file

Order by

Category desc,

Category desc,

P_mnth1 desc;

Quit;

PROC SQL noprint;

Create table work.WANTA As

Select

Category,

Category2,

New Category,

P_mnth1 As MONTH FORMAT= YYMMN6.

From work.New

Where Time ❤️

Group by New Category

Order by

Category DESC,

Category2 Desc,

New Category DESC,

P_mnth1 DESC;

Quit;

PROC SQL noprint;

Create table work.want_FINAL AS

Select

a.*

From work.New AS a

LEFT JOIN work.WANTA As b   ON a.New category = b.New Category;

QUIT;

Waiting for your response. In excel sheet , I attached tab named as earlier output (this code is for the same only modification I did was to concatenate two coulmns and then use the above code but then it is not working) , the tab named as " New Output" is exactly what I am looking now.

Am I able to make you to understand the requirement

[Reeza]

I'm guessing that code still isn't what you ran or has errors. Did you try and fix the errors?

I'm seeing syntax errors immediately - remember to fix the issues in the order you encounter them. If you're not getting errors, please post your full log from the code above. I can't open attachments so I can either try and look at this later tonight, if I remember or you can try and post your data and output as text/images or wait for someone else to see if they can help.

Here are instructions on how to provide sample data as a data step:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

---

@priyanka14 wrote:

Hi ,

 Sorry I shared the same code which I got from sas team.

Now here is the code which I tried. I am attaching the new excel so that it can help you to understand my actual requirement.

A bit change with the requirement. I am trying to write down what extactly I am looking for.

In the Excel sheet , Input tab for category F2, Category 4, I would like to select the continuous 6 rows from P_mnth1 201808. The logic is like I have to first check in column " Time" for each category and its category2 where Time is equal to or greater than 3. From there I have to select 6 rows continuously but if in case Time is "." as p_count is 0 and next row having "Time column"  3 orgreater than 3, then I need to take 6 rows from where time column".". Like in Category F2, Category2 as 4 , I want rows from 082018 as P_count is 0 and next row below it have time 3. Like now for categoryF2 with category2 as 3, I will select rows from 032018.

This what modification I have from earlier query . earlier I was looking the continuous rows where time column is 3 or greater 3 starts . I was not including the row where time column is ".". for ex earlier , with same category F2, Category2 as 4 , I was looking rows from 072018.

Hope this helps in understanding as its difficult to explain in writing all.

The code over which I was trying is earlier one only so for  selecting Continous 6  rows from time 3 or greater than 3  .

Request you to please look at the excel attached. The input and output which I really want.

Here is the code which I was trying .

PROC SQL;

Create Table NEW As

Select Distinct

Category,

Category2,

P_mnth1,

PCount,

Time,

(CAT(Category,Category2)) As New Category

From SAS file

Order by

Category desc,

Category desc,

P_mnth1 desc;

Quit;

PROC SQL noprint;

Create table work.WANTA As

Select

Category,

Category2,

New Category,

P_mnth1 As MONTH FORMAT= YYMMN6.

From work.New

Where Time ❤️

Group by New Category

Order by

Category DESC,

Category2 Desc,

New Category DESC,

P_mnth1 DESC;

Quit;

PROC SQL noprint;

Create table work.want_FINAL AS

Select

a.*

From work.New AS a

LEFT JOIN work.WANTA As b   ON a.New category = b.New Category;

QUIT;

Waiting for your response. In excel sheet , I attached tab named as earlier output (this code is for the same only modification I did was to concatenate two coulmns and then use the above code but then it is not working) , the tab named as " New Output" is exactly what I am looking now.

Am I able to make you to understand the requirement

---

[priyanka14]

I am attaching the snapshot of input and the output which I required. I am sharing the sample data .

Input

If we combine this all snaps , it will be the input . For F4 Category , Category2 are 1,2,3,4 , Then F8 category , having category2 as 4,3. You can arrange the snaps as I am not able to do that so that all F4 category comes first then all F8 category. The highlighted one are what I want in my output

The output which I am looking :

Continous of output

Hope this will help. I am not able to  share the required input.

Thanks

Priyanka

[priyanka14]

Sorry , I know I am not able explain my requirement properly or share the files . But just hoping that through snaps it will somewhat clear that what  I am looking for