Data extraction with counts and sum with similar remarks as common

[Jprathap447]

I have a data with some remarks field as common, I want to know the count of that data and the sum

[Kurt_Bremser]

Hi and welcome to the communities!

Please give us an example for your dataset, ideally in a data step with datalines (so we can easily recreate it for testing).

Use the "little running man" button (right next to the one indicated) to post your code:

The one indicated is best for logs and text data (e.g. from CSV files).

And show us what you expect as a result. If you already tried code to solve your issue, post that also.

[Jprathap447]

I want to know how many C type of apples and the count and sum of the amount... etc

[Kurt_Bremser]

Do you think of something like this:

data have;
infile '/folders/myfolders/datasas.csv' dlm=',' dsd truncover;
input date :mmddyy10. amount name $ type :$1.;
format date yymmddd10.;
run;

proc sql;
create table want as
  select
    name,
    type,
    count(name) as count,
    sum(amount) as amount
  from have
  group by name, type
;
quit;

[Jprathap447]

Q1. I am using windows 10 and the datasas file is on my desktop "C:\Users\prathap\Desktop"; how to map it

[Kurt_Bremser]

Do you have a licensed SAS installation, or University Edition?

If licensed, locally on your desktop or on a server?

If server, do you use SAS Studio or Enterprise Guide?

[Jprathap447]

Licensed SAS installation, on Server, using Enterprise Guide.

[Kurt_Bremser]

Use the File Copy task in Enterprise Guide to copy the file to your server.

Then you can run the code I used, only adapting the location of the csv file.

[Jprathap447]

It is not showing .csv file, it is accepting only SAS database files while browsing to upload file to server.

[Kurt_Bremser]

I am NOT talking about the dataset upload/download, I am talking about the COPY FILES TASK, which is part of the standard EG setup at least since version 7.13. This task can copy ANY file.

[Jprathap447]

I am using 4.3. Thanks for your support.

[Kurt_Bremser]

If you like to live in the past, why do you use Windows 10 instead of XP, which is about as old?

Really, you should update to 7.15 or 8.2 ASAP, you're missing out on a tankerload of features.

And the current version is always included in SAS licenses.

Read the blog article, @ChrisHemedinger also provides the task as an add-on to earlier EG versions.

Without the task, you can always use a SFTP client to upload data to your SAS server, or use a shared network resource that is available both on your desktop and server. In the end, the file has to be "visible" to your SAS session, so you can run my code.

You can also use the import wizard of EG, but you need to supply the column names, types and formats yourself, since your csv file does not have a header line.

[Jprathap447]

can I write steps like using query builder...

[ChrisHemedinger]

Hi @Jprathap447 -

Welcome to the community and to the world of SAS.  The version of SAS Enterprise Guide that you're using is quite old, but you still might benefit from this tutorial.  It's 2+ hours and covers what you need to know about working within the environment, and completing basic tasks like querying and summarizing data.